Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Announcements
Coming your way, the Qlik Data Revolution Virtual Summit. October 27-29. REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Creator
Creator

Conditional Sum in script

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.

1 Solution

Accepted Solutions
Highlighted
MVP
MVP

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;	

Capture.PNG

View solution in original post

7 Replies
Highlighted
Creator III
Creator III

how you are grouping the account id in final table

Highlighted
Creator
Creator

 It is template according account law. It is excel. 

Highlighted
Creator III
Creator III

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

Highlighted
Creator
Creator

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;
Highlighted
MVP
MVP

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;	

Capture.PNG

View solution in original post

Highlighted
Creator
Creator

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.

Highlighted
Creator
Creator

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.