Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hello, I have problems with summing values from 2 tables:
1. table:
Account Money
601100 1000
602200 3000
602300 2000
501300 3000
502100 2000
512001 7000
FINAL Table (summing according to account)
RowNumber Account Sum of Money
1 601 1000
1 602 3000+2000=5000
2 60 1000+3000+2000=6000
3 501 3000
4 502 2000
5 50 3000+2000=5000
6 511 0
7 512 7000
8 51 7000
...
How can I do it in script ??? Could you help me ?
Thank you.
One way could be like:
Table1:
Load * Inline [
Account,Money
601100,1000
602200,3000
602300,2000
501300,3000
502100,2000
512001,7000
];
Join
Table2:
Load RowNumber,
Account as Account2 Inline [
RowNumber,Account
1,601
1,602
2,60
3,501
4,502
5,50
6,511
7,512
8,51
];
Final:
Load
RowNumber,
Account2 as Account,
Sum(Money) as NewMoney
Resident Table1 Where WildMatch(Account, Account2&'*') Group By RowNumber, Account2 Order By RowNumber;
DROP Table Table1;
how you are grouping the account id in final table
It is template according account law. It is excel.
I mean what is the logic behind it if one has to implement the same.
How to identify account id from table 1 will go to which account id of final table
Hi Jozisvk11
Try this:
/*data load*/
t:
load * inline [
accnt,amnt
601100,1000
602200,3000
602300,2000
501300,3000
502100,2000
512001,7000
];
/*create grouping accounts*/
t1:
load
Left(accnt,2) as acc_l1,
Left(accnt,3) as acc_l2,
accnt,
amnt
resident t;
final_tab:
load
acc_l1 as account,
sum(amnt) as amount
resident t1
group by acc_l1;
Concatenate(final_tab)
load
acc_l2 as account,
sum(amnt) as amount
resident t1
group by acc_l2;
drop tables t,t1;
One way could be like:
Table1:
Load * Inline [
Account,Money
601100,1000
602200,3000
602300,2000
501300,3000
502100,2000
512001,7000
];
Join
Table2:
Load RowNumber,
Account as Account2 Inline [
RowNumber,Account
1,601
1,602
2,60
3,501
4,502
5,50
6,511
7,512
8,51
];
Final:
Load
RowNumber,
Account2 as Account,
Sum(Money) as NewMoney
Resident Table1 Where WildMatch(Account, Account2&'*') Group By RowNumber, Account2 Order By RowNumber;
DROP Table Table1;
What could i do if my first table is:
Account Money Impact
601100 1000 Yes
602200 3000 Yes
602300 2000 Yes
501300 3000 No
502100 2000 No
512001 7000 No
How can I write this script because column "IMPACT" need in expression.
Thank you.
Account Money Impact
601100 1000 Yes
602200 3000 Yes
602300 2000 Yes
501300 3000 No
502100 2000 No
512001 7000 Yes
I need column "Impact" in expression. How can i do this in script.
Thank you.