topic Re: Generating Dates in App Development

Generating Dates

dineshraj — Tue, 19 Sep 2017 06:43:55 GMT

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

Re: Generating Dates

hic — Tue, 19 Sep 2017 06:54:40 GMT

How to populate a sparsely populated field

Re: Generating Dates

hic — Tue, 19 Sep 2017 07:28:48 GMT

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;