2 Replies Latest reply: Sep 6, 2018 7:14 AM by Kalmer Kaljuste RSS

    Require help with incremental load when dimension changes

    Kalmer Kaljuste

      Hello!

       

      I'm sturggling to get my code to work. Please help me out guys.
      The issue is that i want to increase the value of the dimension "NewField" everytime the LineNr changes also the field "Controll" is there to let me know when i should increase the the value of the dimension.
      The red defines how it should be done.
      Honestly the Peek function for NewField is not working at all, only the rangesum gives me any result.

       

      concatenate(MasterTable)

      Load

          'Journey' as Fact,

          if(Controll=1, RangeSum(1, peek(NewField)+1),

            if(Controll=0, RangeSum(1, peek(NewField)), Peek(NewField))) as NewField,

          *

      ;

      Load

      if(Previous(_KEY_ProductID_TS_LineNr) = _KEY_ProductID_TS_LineNr, 0, 1) as Controll

      *,

      Resident tmp_Jour2

      order by Timestamp, ProductID, LineNr. asc;


      Untitled.png
      Any help is greatfully accepted!

        • Re: Require help with incremental load when dimension changes
          Felip Drechsler

          Hi Kalmer,

           

          I've made a simple example with some dummy data:

           

          x:
          load
          Fact,
          Date,
          TimeStamp,
          date(Date + TimeStamp,'DD/MM/YYYY hh:mm:ss') as FormattedDate,
          LineNr,
          ProductID;
          load * Inline
          [
          Fact,Date,TimeStamp,LineNr,ProductID
          Journey,01/01/2018,10:05,a,28BE0
          Journey,01/01/2018,10:07,a,28BE0
          Journey,01/01/2018,10:13,b,28BE0
          Journey,01/01/2018,10:25,a,28BE0
          Journey,01/01/2018,13:05,c,28BE0
          Journey,01/01/2018,16:05,a,28BE0
          Journey,01/01/2018,19:00,a,28BE0
          Journey,01/01/2018,22:00,d,28BE0
          ];
          
          
          Control:
          Load
          Fact,
          Date,
          TimeStamp,
          date(Date + TimeStamp,'DD/MM/YYYY hh:mm:ss') as FormattedDate,
          LineNr,
          ProductID,
          Control,
          if
          (
          isNull(peek(NewField)),
          1,
          if
          (
          Control=1,
          peek(NewField)+1,
          peek(NewField)
          )
          ) as NewField;
          load
          *,
          if (peek(LineNr)=LineNr,1,0) as Control
          Resident x
          order by FormattedDate;
          
          
          drop tables x;
          

           

          The above script gives me the following:

          sample.png

          Felipe.