Pls help: Tricky Calculation

[Anonymous]

I have a scenario where I attached Excel file with sample data and Expected result , Here every 12th record should subtract 1st record of score column and 24th record should subtract 13th record and  so on. If there are no enough values then it should get the maximum value of the column. In this we have create two columns i.e Base and Newscore. All these stuff needs to be achieved in load script only.

John

[petter]

This is a script that works as expected with your data sample in the spreadsheet:

[DATA_]:
LOAD 
  *,
  If( RecNo() = 0 , Score , If( Peek('MaxScore') > Score , Peek('MaxScore') , Score ) ) AS MaxScore
INLINE [
Day Date Score
Day 1 01.01.2017 430
Day 2 02.01.2017 990
Day 3 03.01.2017 958
Day 4 04.01.2017 984
Day 5 05.01.2017 984
Day 6 06.01.2017 984
Day 7 07.01.2017 932
Day 8 08.01.2017 872
Day 9 09.01.2017 925
Day 10 10.01.2017 930
Day 11 11.01.2017 930
Day 12 12.01.2017 1069
Day 13 13.01.2017 930
Day 14 14.01.2017 930
Day 15 15.01.2017 901
Day 16 16.01.2017 950
Day 17 17.01.2017 890
Day 18 18.01.2017 890
Day 19 19.01.2017 890
Day 20 20.01.2017 890
Day 21 21.01.2017 890
Day 22 22.01.2017 887
Day 23 23.01.2017 897
Day 24 24.01.2017 953
Day 25 25.01.2017 951
Day 26 26.01.2017 951
Day 27 27.01.2017 951
Day 28 28.01.2017 951
Day 29 29.01.2017 1170
Day 30 30.01.2017 921
Day 31 31.01.2017 915
] (delimiter is \t);

LastRow = NoOfRows('DATA_');
MaxScore = Peek('MaxScore');
DROP FIELD MaxScore;

[DATA]:
LOAD
  *,
  If( RecNo() = $(LastRow) , $(MaxScore) ,
  If( Mod(RecNo(),12)=0 , 
  Score - Peek('Score',RecNo()-12,'DATA_') )) AS [New Score]
RESIDENT
  DATA_;  

DROP TABLE DATA_;  

[Anonymous]

Hi Peter ,

could you please share your  sample qvw

tnx

[petter]

Should be easy for you to just copy from above and paste into your load script but here you are....