Can someone helps me with how to do this in Qlik Sense load script?

[Qiyanlu66]

Hi everyone,

Here is my original table,

Item Number	Date	Total	Minus
A	2020-01-01	45
A	2020-01-02		5
A	2020-01-03
A	2020-01-04
B	2020-01-01	100
B	2020-01-02
B	2020-01-03		10
B	2020-01-04

Here is the result table that I want.

Item Number	Date	Total	Minus
A	2020-01-01	45
A	2020-01-02	40	5
A	2020-01-03	40
A	2020-01-04	40
B	2020-01-01	100
B	2020-01-02	100
B	2020-01-03	90	10
B	2020-01-04	90

Can someone please help me with how to write the load script to convert the first table to the second one? Or maybe some ideas would be very much appreciate.

Thanks for any helps.

Yan

[Kushal_Chawda]

@Qiyanlu66  try below. Assuming your Date field is in proper Date format

Data:
LOAD [Item Number], 
     Date, 
     Total, 
     Minus
FROM
[https://community.qlik.com/t5/New-to-Qlik-Sense/Can-someone-helps-me-with-how-to-do-this-in-Qlik-Sense-load/td-p/1747844]
(html, utf8, UserAgent is 'Mozilla/5.0', embedded labels, table is @1);

Final:
LOAD *,
     if([Item Number]<>Previous([Item Number]),rangesum(Total,-Minus), rangesum(Peek(Total_New),-Minus)) as Total_New
Resident Data
Order by [Item Number],Date;

DROP Table Data;

[stascher]

If you're a fan of SQL joins you can do something like this.

Data:
LOAD [Item Number], 
     Date, 
     Total, 
     Minus
FROM
[https://community.qlik.com/t5/New-to-Qlik-Sense/Can-someone-helps-me-with-how-to-do-this-in-Qlik-Sense-load/td-p/1747844]
(html, utf8, UserAgent is 'Mozilla/5.0', embedded labels, table is @1);


left join 
load [Item Number], max(Total) as MaxTotal
resident Data group by [Item Number];

left Join
load [Item Number], Date as MinusDate, Minus as MinusVal
resident Data where len(Minus)>0;

left Join
load [Item Number], Date, if(Date>=MinusDate,MaxTotal-MinusVal,MaxTotal) as Result
resident Data;

Final:
load [Item Number],Date,Result as Total, Minus resident Data;

drop table Data;