3 Replies Latest reply: Dec 12, 2016 1:46 AM by Maciej Kaniewski RSS

    Calculations on rows in Load script

    Maciej Kaniewski

      Hi all,

       

      I recently had a quite difficult calculation issue which you may find interesting.

      What is more - I'm not proud of my final script so maybe you can provide a better solution.

       

      My source data looks like this:

       

      RowNrProductIDDataFromDataToValue
      1104.01.201609.01.201614
      2105.01.201610.01.201654
      3108.01.201616.01.201636
      4110.01.201618.01.201675
      5115.01.201618.01.201639
      6116.01.201620.01.201676
      7118.01.201624.01.201634
      8119.01.201626.01.201665
      9120.01.201626.01.201654
      10124.01.201628.01.201666

       

      All I need to do is that for all records I have to calculate Sum of all previous records which meet the condition:

       

      DateTo from previous records has to be between DateFrom and DateTo for calculated row,

      that means, for row with RowNr = 6 I am looking for all rows with DateTo between 16.01.2016 and 20.01.2016,

      so my calculated value will be: 226 (36+75+39+76)


      In that way I'll get:

       

      RowNrProductIDDateFromDateToValue

      SumOfValue


      1104.01.201609.01.20161414
      2105.01.201610.01.20165468
      3108.01.201616.01.201636104
      4110.01.201618.01.201675165
      5115.01.201618.01.201639

      190

      6116.01.201620.01.201676226
      7118.01.201624.01.201634224
      8119.01.201626.01.201665175
      9120.01.201626.01.201654229
      10124.01.201628.01.201666219


      My script looks like this:

       

      Load RowNr, DateFrom, DateTo, Value,

          if(ProductID=peek(ProductID, -1) and peek(DateTo, -1) <= DataTo and peek(DateTo, -1) >= DateFrom, peek(Value, -1) +

          if(ProductID=peek(ProductID, -2) and peek(DateTo, -2) <= DataTo and peek(DateTo, -2) >= DateFrom, peek(Value, -2) +

          if(ProductID=peek(ProductID, -3) and peek(DateTo, -3) <= DataTo and peek(DateTo, -3) >= DateFrom, peek(Value, -3) +

          if(ProductID=peek(ProductID, -4) and peek(DateTo, -4) <= DataTo and peek(DateTo, -4) >= DateFrom, peek(Value, -4) +

          if(ProductID=peek(ProductID, -5) and peek(DateTo, -5) <= DataTo and peek(DateTo, -5) >= DateFrom, peek(Value, -5) +

          if(ProductID=peek(ProductID, -6) and peek(DateTo, -6) <= DataTo and peek(DateTo, -6) >= DateFrom, peek(Value, -6) +

          if(ProductID=peek(ProductID, -7) and peek(DateTo, -7) <= DataTo and peek(DateTo, -7) >= DateFrom, peek(Value, -7) +

           + Value as SumOfValue

      From ...


      Data is ordered by ProductID and DateTo.

      Let's assume that in data source there will be no more than 7 rows to check.

      I've got milions of rows because of many ProductID.

      Calculations have to be done in script. I can't do it with Set Analysis in chart.


      Now I am looking for better solution.

      Peek function works pretty fast but I believe it's not the best soluton and I know script masters can do it better.

      I also predict that I will have to calculate more than only 7 rows in the future, so my script is not scalable.

      I tried loops, joins, interval match but all of them are too heavy and can't calculate milions of loops for each row in real time.


      Any advices with script functions will be appreciated!


      Thank you.