2 Replies Latest reply: Sep 19, 2017 3:28 AM by Henric Cronström RSS

    Generating Dates

    dineshraj ramesh

      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
          Henric Cronström

          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;