topic Pls help: Tricky Calculation in QlikView

Pls help: Tricky Calculation

Anonymous — Tue, 21 Feb 2017 17:44:28 GMT

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

Re: Pls help: Tricky Calculation

petter — Tue, 21 Feb 2017 18:53:51 GMT

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_;

Re: Pls help: Tricky Calculation

Anonymous — Tue, 21 Feb 2017 19:15:03 GMT

Hi Peter ,

could you please share your sample qvw

tnx

Re: Pls help: Tricky Calculation

petter — Wed, 22 Feb 2017 01:06:16 GMT

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