Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 
Rsaiq
Creator
Creator

Sum up values of duplicates row

Hi All,

Rsaiq_2-1627229585689.png

 

As you can see above ,i have columns in dataset as ScriptID,Account,Date,Type and Amount columns.

I want to create 2 new columns i.e
1.Amount13405(In this column i am showing  amount for 13405 account and 0 for other account)
2.Closing(Summing all amounts of Amount13405 columns values ScriptID wise,you can see in above image)

Used below script for creating Amount13405 and Closing columns in qliksense

Test:
load *,
if(previous(ScriptID) = ScriptID, rangesum(peek('Closing'),Amount13405),Amount13405) as Closing;

LOAD
ScriptID,
Account,
"Date",
"Type",
Amount,
if(Account='13405',Amount,'0') as Amount13405
FROM [lib://AttachedFiles/Demo.xlsx]
(ooxml, embedded labels, table is Sheet2);

With the help of above scripts i have achived the same output in qliksense as excel(refer screenshot above ) 

Rsaiq_1-1627229258759.png

But when multiple lines coming then calculations are not working fine.

Rsaiq_6-1627231425463.png

 

if i pick 1 example for multiple line from above image, line number 6,7 and 8 are reapeating from here onwards calculations are not working correctly

Qliksense table with multiple line below :

Rsaiq_7-1627232087562.png

 

In short,if we have single lines in dataset then its working fine and if multiple lines then calculation are not working as expected.
Kindly find dataset file in the attachment and Please help me to resolved this issue

Thanks

 

 

 

Labels (4)
0 Replies