Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi All,
I need to create a field as New_Amount from qliksense script by adding previous record per id.
I am sharing the dataset and Expected result for more clarifications
My Dataset:
Expected Result:
Please find sample data in the attachment.
Thanks in advance.
Try this
tmp:
LOAD ID,
Date_1,
Date_2,
Status,
Amount
FROM
.......
;
T:
load
*,
If(ID=Peek('ID'), Peek('Amount')) as New_Amount // get the previous Amount only if ID = previous ID
Resident tmp
order by ID, Date_2; // I think you want the previous amount sorting by Date_2
drop table tmp;
This should give you what you're looking for.
LOAD
ID,
Date_1,
Date_2,
Status,
Amount,
If(ID=Previous(ID),Previous(Amount),0) as New_Amount
FROM [lib://DataFiles/Sample Data.xlsx]
(ooxml, embedded labels, header is 1 lines, table is Sheet1);
Try this
tmp:
LOAD ID,
Date_1,
Date_2,
Status,
Amount
FROM
.......
;
T:
load
*,
If(ID=Peek('ID'), Peek('Amount')) as New_Amount // get the previous Amount only if ID = previous ID
Resident tmp
order by ID, Date_2; // I think you want the previous amount sorting by Date_2
drop table tmp;
This should give you what you're looking for.
LOAD
ID,
Date_1,
Date_2,
Status,
Amount,
If(ID=Previous(ID),Previous(Amount),0) as New_Amount
FROM [lib://DataFiles/Sample Data.xlsx]
(ooxml, embedded labels, header is 1 lines, table is Sheet1);