Skip to main content
Announcements
Accelerate Your Success: Fuel your data and AI journey with the right services, delivered by our experts. Learn More
cancel
Showing results for 
Search instead for 
Did you mean: 
rakeshkumar1890
Creator
Creator

Subtract Previous Row

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

 

Labels (3)
5 Replies
BrunPierre
Partner - Master
Partner - Master

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

rakeshkumar1890
Creator
Creator
Author

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
Partner - Master
Partner - Master

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

peter_brown_0-1662010095720.png

 

rakeshkumar1890
Creator
Creator
Author

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
Creator
Creator
Author

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 ;