Generating Dates

[dineshraj]

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

[hic]

How to populate a sparsely populated field

[hic]

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;