Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi Experts,
I need to fill dates till this month with Price Value
I have data like this
Material Std Price Price Date
101A 10 1/Jul/2017
101A 10 1/Aug/2017
101B 9 1/Aug/2017
101C 12 1/Jul/2017
I need Output Like this
Material Std Price Price Date
101A 10 1/Jul/2017
101A 10 1/Aug/2017
101A 10 1/Sep/2017
101B 9 1/Aug/2017
101B 9 1/Sep/2017
101C 12 1/Jul/2017
101C 12 1/Aug/2017
101C 12 1/Sep/2017
In your case you have both Material and Date to consider, so you need the Cartesian product of the two:
tmpData:
Load
Material,
Date#([Price Date],'D/MMM/YYYY') as [Price Date],
[Std Price]
Inline
[Material,Std Price,Price Date
101A,10, 1/Jun/2017
101A,11, 1/Aug/2017
101B, 9, 1/Aug/2017
101C, 12, 1/Jul/2017
101C, 13, 1/Sep/2017];
MinMaxDate:
Load Min([Price Date]) as MinDate, Max([Price Date]) as MaxDate resident tmpData;
Let vMinDate = Peek('MinDate',-1,'MinMaxDate') ;
Let vMaxDate = Peek('MaxDate',-1,'MinMaxDate') ;
tmpCartesianProduct:
Load distinct Material Resident tmpData;
Join
Load Date(AddMonths($(vMinDate),IterNo()-1),'D/MMM/YYYY') as [Price Date] Autogenerate 1
While AddMonths($(vMinDate),IterNo()-1) <= $(vMaxDate);
Join (tmpData) Load * Resident tmpCartesianProduct;
Data:
NoConcatenate Load
Material,
[Price Date],
If(not IsNull([Std Price]),[Std Price], If(Material=Peek(Material),Peek([Std Price]))) as [Std Price]
Resident tmpData
Order By Material, [Price Date];
Drop Table MinMaxDate, tmpCartesianProduct, tmpData;