5 Replies Latest reply: Jan 10, 2018 9:58 AM by Anil Samineni RSS

    Add the new rows with the value of rolling average value

    Yong Han

      Let's say I have a Currency table which can be simplified like below:

        

      daterate currency
      ….………..
      28.12.20177,4455DKK
      29.12.20177,4449DKK
      30.12.20177,4449DKK
      31.12.20177,4449DKK
      1.1.20187,4449DKK
      2.1.20187,4437DKK
      3.1.20187,4442DKK
      4.1.20187,4449DKK
      5.1.20187,4459DKK
      6.1.20187,4459DKK
      7.1.20187,4459DKK
      8.1.20187,4467DKK

       

      Let's assume the date start from 01.01.2016, I need to extend the data to one year in the future e.g. until 8.1.2019, and set the rate be the rolling average of the history data in last 365 days.

       

      what I did was as below,

      table_temp:

       

      LOAD

      date,

      Currency,

      rate ,

      FROM sourceData.qvd

      where Currency_id = 'DKK'

      ;

      join


      LOAD

      date(Today()-2 + iterNo())  as date,

      'DKK' as currency

      AUTOGENERATE 1

      While date(Today()-2 + iterNo()) <= AddYears(Today()-2,1);

       

      currency:

      NOCONCATENATE

      load

           date,

            currency,

            if(IsNull(rate), RangeAvg(rate), rate) as rate,  

            currency_key

       

      Resident table_temp

      order by exchange_rate_date;

       

      drop Table table_temp;



      but I don't get, why rangeAvg(rate) did not work, instead it only returned the rate value from previous row.

      Could anyone give some help? How can I get rate value from the last year's rolling average? Thanks!