How to handle count day(s), when occurs two specific scenarios

[jozef_klimo]

Hey guys,

We have Inventory balance fetched and stored to .qvd files per daily basis.

Basically I would like to get value for count day(s) from the newest date Today(1) until the oldest one with the same quantity level. (until first entry where balance is different from that newest one) 

&

And also value for count day(s), where today's stock balance  is greater than zero until first "empty" date cell.

I hope you can understand what I would like to accomplish with this.

If you are lost I have prepared picture for this.

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 🙂

[rubenmarin · Accepted solution]

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;