Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements
QlikWorld 2020: Join us May 11 - 14, 2020 in Phoenix, AZ. Register early and save $400. Learn More
pjn123pjn123
New Contributor III

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

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!

table.png

1 Solution

Accepted Solutions

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

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;

View solution in original post

3 Replies

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

Would you share excel file

Before develop something, think If placed (The Right information | To the right people | At the Right time | In the Right place | With the Right context)

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

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;

View solution in original post

pjn123pjn123
New Contributor III

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

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