2 Replies Latest reply: Aug 9, 2017 7:54 AM by Holger Schmolzi RSS

    Get the average of values 5 steps backwards in the script

    Holger Schmolzi

      I want to build an average 5 steps backwards of a field value with a flag in another column and load the result in a separate table.

       

      For example

      Table1:

       

      Nrvalueflag
      1100
      2110
      3120
      4100
      5110
      691
      780
      870
      9100
      1030
      1120
      1210
      1311
      1470

       

      I’m looking for the average 5 steps backwards from line 6 and line 13.

       

      Average_Table:

      Nraverage
      610,6
      133,4

                   

      I need the Average_Table in the script, not in the layout area.

          

      I try to do it in this way, but the result is not what I want to get:

      //******************************************************************* 

      Tab1:
      LOAD * INLINE [
      Nr, value, flag
      1, 10, 0
      2, 11, 0
      3, 12, 0
      4, 10, 0
      5, 11, 0
      6, 9, 1
      7, 8, 0
      8, 7, 0
      9, 10, 0
      10, 3, 0
      11, 2, 0
      12,  1,   0
      13,  1,   1
      14,  7,   0
      ]
      ;

      Tab2:
      load *,
      rowno(),
      RecNo()
      resident Tab1;

      average_tab:
      load
      avg(value) as Average,
      (
      FieldIndex('flag','1')) as Index
      resident Tab2

       

      where
      RecNo()<=(FieldIndex('flag','1'))
      and
      RecNo()>=(FieldIndex('flag','1')-5)

      ;