Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi Experts
i need some help with writing different kind of loop
for first record of every entity it has to calculate Transaction Amount + Initial amount into new column as EOP
from second record it has to be sum(first record in EOP) + 2nd record in initial Amount
i am attaching xls
i am trying to generate EOP column using Qlik Script not in report level
Hi, try this:
temp:
LOAD
SubBank,
YearMonth,
[Transaction Amount],
Initial_Amount,
if(SubBank<>Previous(SubBank) or RowNo()=1, [Transaction Amount]+Initial_Amount, Initial_Amount) as sumTrIn,
EOP
FROM [lib://AttachedFiles/Help.xlsx](ooxml, embedded labels, table is Sheet1);
NoConcatenate
main:
LOAD
SubBank,
YearMonth,
[Transaction Amount],
Initial_Amount,
if(SubBank=Previous(SubBank), rangesum(sumTrIn ,peek('EOP')), sumTrIn) as EOP
RESIDENT temp;
DROP TABLE temp;