topic Re: Complexe sum script in QlikView

Complexe sum script

didierodayo — Thu, 14 Jul 2016 09:38:05 GMT

Hi Experts,

I am faced with a small challenge. I have the table below which holds ID;DATE; PRICE.

ID	DATE	PRICE
00234	15/02/2016	125
00234	21/03/2016	250
00234	22/03/2016	300
00234	28/03/2016	400
00234	17/04/2016	900
00234	25/04/2016	3500
00234	05/05/2016	4000
00234	15/05/2016	6000
00234	21/05/2016	750
00234	06/06/2016	200
00234	20/06/2016	120
00234	01/07/2016	45

question1: while loading the table I would like to return only the id;date;price While ignoring the minimum dates up to where the SUM of price is >=600 and <=700. The result will be :

Question2: From the original I would like to load only the id;date;price where the Sum of Price for any consecutive 3 dates is >=10000.

the expected result will be.

I would appreciate any help.

Regards

Re: Complexe sum script

sunny_talwar — Thu, 14 Jul 2016 09:48:28 GMT

I think its difficult to understand your output here. How is 45 included in your first screenshot. Can you elaborate on the thinking behind the two questions you have here?

Re: Complexe sum script

tresB — Thu, 14 Jul 2016 09:57:08 GMT

I could not get your question1:

Didier Agonvonon wrote:

...While ignoring the minimum dates up to where the SUM of price is >=600 and <=700. The result will be :

How does your logic give the output? Could you please explain?

Re: Complexe sum script

didierodayo — Thu, 14 Jul 2016 09:59:13 GMT

Hi Sunny,

I am trying this is actually a sample of a very large table. so I would like to ignore the FIRST dates for all IDs up to >=600 and <=700. So the load will ignore 125;250;300 (with their corresponding id and dates)

Secondly it is suspicious to have consecutive amount up to >=10000 . so I would like to identify any consecutive sum that equals to >=10000 which is the result is the 3rd snapshot.

I hope it is clearer now otherwise please let me know.

Thanks

Re: Complexe sum script

didierodayo — Thu, 14 Jul 2016 10:02:09 GMT

Hi Tresesco,

Please check my reply to Sunny, I am only ignoring the beginning (first rows) up to the sum of >=600 and <=700.

Re: Complexe sum script

tresB — Thu, 14 Jul 2016 10:02:40 GMT

But the first value - (after 125;250;300) 400 doesn't satisfy the condition >=600 and <=700 ??

Re: Complexe sum script

sunny_talwar — Thu, 14 Jul 2016 10:04:39 GMT

I guess 125;250;300 are getting ignored, but how is 400 meeting the condition? >=600 and <= 700?

Re: Complexe sum script

didierodayo — Thu, 14 Jul 2016 10:07:04 GMT

the sum of 125;250;300 is 675. so am ignoring those 3 rows before the load.

Re: Complexe sum script

didierodayo — Thu, 14 Jul 2016 10:09:10 GMT

Hi Sunny, am not ignoring 400. you can see it is showing up the result in the second snapshot( result 1)

Re: Complexe sum script

Gysbert_Wassenaar — Thu, 14 Jul 2016 10:09:51 GMT

I think you want a cumulative sum of price:

Temp1:

LOAD

ID,

DATE,

PRICE

FROM

...source...

;

Temp2:

LOAD

ID,

DATE,

PRICE,

If(ID = Previous(ID), Rangesum(PRICE, peek(CUM_PRICE)) , PRICE) as CUM_PRICE,

If(ID = Previous(ID), Rangesum(PRICE, Peek(PRICE, -1), Peek(PRICE, -2))) as CONSEC_3DAY_SUM

RESIDENT

Temp1

ORDER BY

ID, DATE

;

DROP TABLE Temp1;

Result:

LOAD

If(CONSEC_3DAY_SUM>=10000 or Peek(CONSEC_3DAY_SUM,-1) >= 10000 or Peek(CONSEC_3DAY_SUM,-2) >= 10000, 1, 0) as CONSEC_3DAY_FLAG

RESIDENT

Temp2

ORDER BY

ID, DATE desc

;

DROP TABLE Temp2;

Once you have this you should do the rest in chart objects imho. For example a straight table with ID and DATE as dimensions and as expression sum({<CUM_PRICE={'>700'}>}PRICE). For the second question use sum({<CONSEC_3DAY_FLAG={1}>}PRICE)

Re: Complexe sum script

sunny_talwar — Thu, 14 Jul 2016 10:10:22 GMT

If for some reason your first 3 data points were like this:

125;250;400 totaling to 775, nothing would have been ignored?

Re: Complexe sum script

didierodayo — Thu, 14 Jul 2016 10:30:02 GMT

Yes Sunny that is correct the requirement is such that if it is above 700 then it should be ignored.

Re: Complexe sum script

didierodayo — Thu, 14 Jul 2016 10:33:03 GMT

Hi GD,

I will try this and confirm . at first look I think it could fix the second question. for the first question we are dealing only with the beginning row where the total is >=600 and <=700. So if any other rows on the table were to be equal to 600 they won't be ignored.

Thanks

Re: Complexe sum script

sunny_talwar — Thu, 14 Jul 2016 10:44:34 GMT

This should meet your first requirement:

Table:

LOAD ID,

DATE,

PRICE

FROM

[https://community.qlik.com/thread/225056]

(html, codepage is 1252, embedded labels, table is @1);

TEMP_TABLE:

LOAD ID,

DATE,

PRICE,

If(ID = Previous(ID), RangeSum(Peek('CUM_PRICE'), PRICE), PRICE) as CUM_PRICE,

If(If(ID = Previous(ID), RangeSum(Peek('CUM_PRICE'), PRICE), PRICE) >= 600 and

If(ID = Previous(ID), RangeSum(Peek('CUM_PRICE'), PRICE), PRICE) <= 700, 1, 0) as FLAG

Resident Table

Order By ID, DATE;

TEMP2_TABLE:

LOAD *,

If(ID = Previous(ID), If(FLAG = 1, FLAG, Peek('NEW_FLAG')), FLAG) as NEW_FLAG

Resident TEMP_TABLE

Order By ID, DATE desc;

FINAL_TABLE:

NoConcatenate

LOAD *

Resident TEMP2_TABLE

Where NEW_FLAG = 0;

DROP Table Table, TEMP_TABLE, TEMP2_TABLE;

Re: Complexe sum script

tresB — Thu, 14 Jul 2016 10:50:24 GMT

Perhaps a peek() is missing:

If(ID = Previous(ID), Rangesum(PRICE, peek(CUM_PRICE)) , PRICE) as CUM_PRICE,

Re: Complexe sum script

Gysbert_Wassenaar — Thu, 14 Jul 2016 10:54:08 GMT

Thanks! Fixed above.