Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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!
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;
Would you share excel file
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;
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