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

[pjn123pjn123]

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!

[Anil_Babu_Samineni]

Would you share excel file

[sunny_talwar · Accepted solution]

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;

[pjn123pjn123]

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