topic Re: Split table row(s) into multiple rows on script load in QlikView

Split table row(s) into multiple rows on script load

pjn123pjn123 — Wed, 25 Nov 2020 16:16:04 GMT

Hi everyone

I have the following problem.

Some of the lines in my source table needs to be split according to percentages in another table.

I tried to demonstrate what I had in mind below.

I'm not sure what way I should tackle this.

Will someone be able to point me in the right direction?

Thanks in advance!

Re: Split table row(s) into multiple rows on script load

Anil_Babu_Samineni — Mon, 27 Mar 2017 15:17:32 GMT

Would you share excel file

Re: Split table row(s) into multiple rows on script load

sunny_talwar — Mon, 27 Mar 2017 15:29:32 GMT

May be try this

Trans_Table:

LOAD * INLINE [

Transaction #, Split Type, Person, Amount

1, , Person A, 1500

2, Type 1, , 500

3, , Person C, 750

4, , Person D, 1000

5, Type 2, , 2000

];

Split_Table:

LOAD * INLINE [

Split Type, Person, Percentage

Type 1, Person B.1, 30%

Type 1, Person B.2, 20%

Type 1, Person B.3, 50%

Type 2, Person E.1, 75%

Type 2, Person E.2, 25%

];

Left Join (Split_Table)

LOAD [Transaction #],

[Split Type],

Amount

Resident Trans_Table

Where Len(Trim(Person)) = 0;

FinalTable:

NoConcatenate

LOAD *

Resident Trans_Table

Where Len(Trim(Person)) > 0;

Concatenate(FinalTable)

LOAD [Transaction #],

Person,

[Split Type],

Amount * Percentage as Amount

Resident Split_Table;

DROP Tables Split_Table, Trans_Table;

Re: Split table row(s) into multiple rows on script load

pjn123pjn123 — Mon, 27 Mar 2017 20:37:29 GMT

Hi Sunny

Cleaver solution! Thanks for your time and help!

Don't know where I will be without this community.

Hopefully I can someday pay it forward.

Cheers