Subtract Previous Row

[rakeshkumar1890]

Hi Folks,

As I am trying to get below output from the below script but not getting desired value after same WeekNo.

required highlighted (Green Color) value.

Thanks 

Inbound_1:
Load
RowNo() as RN,
etadate,
WeekNo,
componentpartnumber,
location,
componentpartnumber & location & WeekNo as Key ,
Sum(quantity) as TotalQuantity
Resident Inbound
Group By etadate, WeekNo, componentpartnumber, location
Order By location, componentpartnumber, WeekNo, etadate asc;

Left Join(Inbound_1)
Load
Key,
Count(WeekNo) as WeekCount
Resident Inbound_1
Group By Key;

Left Join(Inbound_1)
Load
Key,
WeekCount,
Max(TotalQuantity) as TotalQuantity_Max,
Min(TotalQuantity) as TotalQuantity_Min
Resident Inbound_1
Group By Key,WeekCount;

Left Join(Inbound_1)
Load
Key,
If(WeekCount=1, TotalQuantity - Previous(TotalQuantity),
TotalQuantity_Max-TotalQuantity_Min) as ChangeInventory
Resident Inbound_1
Order By RN ;

Drop Table Inbound;
Drop Field Key;

componentpartnumber	etadate	location	RN	TotalQuantity	TotalQuantity_Max	TotalQuantity_Min	WeekCount	WeekNo	ChangeInventory (Current Output)	ChangeInventory (Required Output)
Q0U57	2022-01-07	xyz	1	92	92	92	1	2	-	-
Q0U57	2022-01-13	xyz	2	58	58	58	1	3	-34	-34
Q0U57	2022-01-28	xyz	3	42	42	42	1	5	-16	-16
Q0U57	2022-02-03	xyz	4	176	176	176	1	6	134	134
Q0U57	2022-02-07	xyz	5	378	378	378	1	7	202	202
Q0U57	2022-04-12	xyz	6	66	82	22	3	16	60	60
Q0U57	2022-04-14	xyz	7	22	82	22	3	16	60	60
Q0U57	2022-04-15	xyz	8	82	82	22	3	16	60	60
Q0U57	2022-07-20	xyz	9	16	16	16	1	30	-66	(16-60)
Q0U57	2022-07-27	xyz	10	96	96	96	1	31	80	80
Q0U57	2021-08-05	xyz	11	3360	3360	10	4	32	3350	3350
Q0U57	2022-08-03	xyz	12	58	3360	10	4	32	3350	3350
Q0U57	2022-08-04	xyz	13	10	3360	10	4	32	3350	3350
Q0U57	2022-08-05	xyz	14	44	3360	10	4	32	3350	3350
Q0U57	2021-12-08	xyz	15	230	230	230	1	50	186	(230-3350)
Q0U57	2021-12-13	xyz	16	58	58	58	1	51	-172	-172

[BrunPierre]

How did you arrive at those highlighted (Green Color) values? Not understanding fully.

[rakeshkumar1890]

Hi Peter,

If you check my script, there is way where I reach the output but not the correct in green color

If(WeekCount=1, TotalQuantity - Previous(TotalQuantity),
TotalQuantity_Max-TotalQuantity_Min) as ChangeInventory

[BrunPierre]

What of sorting on the WeekCount field in asc at UI?

[rakeshkumar1890]

Using Weekcount to calculate ChangeInventory values

In Script:

Using Key-

componentpartnumber & location & WeekNo as Key

Left Join(Inbound_1)
Load
Key,
Count(WeekNo) as WeekCount
Resident Inbound_1
Group By Key;

[rakeshkumar1890]

Shorting by RowNo in script level

Left Join(Inbound_1)
Load
Key,
If(WeekCount=1, TotalQuantity - Previous(TotalQuantity),
TotalQuantity_Max-TotalQuantity_Min) as ChangeInventory
Resident Inbound_1
Order By RN ;