Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
TempTable:
Load
Hist_StockPlaceInv_Date,
ID_ItemKey,
Num(Hist_StockPlaceInv_StockPlaceQty) as Hist_StockPlaceInv_StockPlaceQty,
Hist_StockPlaceInv_PlaceName,
Hist_StockPlaceInv_StockPlaceSeq as StockPlaceSeqLink
FROM [lib://QV_Transform_Inventory_QVD/HIST_StockPlaceInventory_*.qvd](qvd)
Where match(Hist_StockPlaceInv_StockCode,4,12,20,29,40);
Join(TempTable)
LOAD
Max(Hist_StockPlaceInv_Date) as MaxDate,
Min(Hist_StockPlaceInv_Date) as MinDate,
FirstSortedValue(Hist_StockPlaceInv_StockPlaceQty, -Hist_StockPlaceInv_Date) as MaxDateQty,
If(Max(Hist_StockPlaceInv_Date) = Today(1),1,0) as ActiveFlag,
ID_ItemKey,
Hist_StockPlaceInv_PlaceName,
Hist_StockPlaceInv_StockPlaceQty
Resident TempTable
Group By ID_ItemKey,Hist_StockPlaceInv_PlaceName,Hist_StockPlaceInv_StockPlaceQty;
Result should look like this.
Thank you for your support 🙂
Hi, I think this will be a lot easier if have it precalculated in script, if user wants real actual data and doesn't needs to know which values were five or ten days ago.
So based on they only need actual data you can do a sorted load by item, place and date desc. Using peek() check previous value to count changes, as a sample:
DataOrig:
LOAD * Inline [
Item ,Place ,Date ,Balance
1 ,100 ,13/04/2020 ,3
1 ,100 ,14/04/2020 ,3
1 ,100 ,15/04/2020 ,3
1 ,100 ,16/04/2020 ,2
1 ,100 ,17/04/2020 ,2
2 ,100 ,13/04/2020 ,2
3 ,100 ,09/04/2020 ,5
3 ,100 ,10/04/2020 ,5
3 ,100 ,11/04/2020 ,5
3 ,100 ,12/04/2020 ,5
3 ,100 ,15/04/2020 ,2
3 ,100 ,16/04/2020 ,2
3 ,100 ,17/04/2020 ,2
];
CalculateStockDays:
LOAD
Item,
Place,
Date,
Balance,
If(Date=Today()
,1
,If(Peek(Item)=Item and Peek(Place)=Place and Peek(Date)=Date+1 and Balance=Peek(Balance) and Peek(LastBalanceChange)>0
,Peek(LastBalanceChange)+1
)) as LastBalanceChange,
If(Date=Today()
,1
,If(Peek(Item)=Item and Peek(Place)=Place and Peek(Date)=Date+1 and Balance<>0 and Peek(BalanceNotZero)>0
,Peek(BalanceNotZero)+1
)) as BalanceNotZero
Resident DataOrig
Order By Item, Place, Date Desc;
Left Join (DataOrig)
LOAD
Item,
Place,
Max(LastBalanceChange) as LastBalanceChange,
Max(BalanceNotZero) as BalanceNotZero
Resident CalculateStockDays
Group By Item, Place;
DROP Table CalculateStockDays;
Hi, I think this will be a lot easier if have it precalculated in script, if user wants real actual data and doesn't needs to know which values were five or ten days ago.
So based on they only need actual data you can do a sorted load by item, place and date desc. Using peek() check previous value to count changes, as a sample:
DataOrig:
LOAD * Inline [
Item ,Place ,Date ,Balance
1 ,100 ,13/04/2020 ,3
1 ,100 ,14/04/2020 ,3
1 ,100 ,15/04/2020 ,3
1 ,100 ,16/04/2020 ,2
1 ,100 ,17/04/2020 ,2
2 ,100 ,13/04/2020 ,2
3 ,100 ,09/04/2020 ,5
3 ,100 ,10/04/2020 ,5
3 ,100 ,11/04/2020 ,5
3 ,100 ,12/04/2020 ,5
3 ,100 ,15/04/2020 ,2
3 ,100 ,16/04/2020 ,2
3 ,100 ,17/04/2020 ,2
];
CalculateStockDays:
LOAD
Item,
Place,
Date,
Balance,
If(Date=Today()
,1
,If(Peek(Item)=Item and Peek(Place)=Place and Peek(Date)=Date+1 and Balance=Peek(Balance) and Peek(LastBalanceChange)>0
,Peek(LastBalanceChange)+1
)) as LastBalanceChange,
If(Date=Today()
,1
,If(Peek(Item)=Item and Peek(Place)=Place and Peek(Date)=Date+1 and Balance<>0 and Peek(BalanceNotZero)>0
,Peek(BalanceNotZero)+1
)) as BalanceNotZero
Resident DataOrig
Order By Item, Place, Date Desc;
Left Join (DataOrig)
LOAD
Item,
Place,
Max(LastBalanceChange) as LastBalanceChange,
Max(BalanceNotZero) as BalanceNotZero
Resident CalculateStockDays
Group By Item, Place;
DROP Table CalculateStockDays;