topic Re: Check each row value between flags and update the Value field in QlikView

Check each row value between flags and update the Value field

quser0098 — Sat, 16 Nov 2024 00:43:59 GMT

Hi devs,

Trying to update the Value column with the highest value for the product by looking into the rows between the flag 1 and -1 in the edit script. I managed to get the output in the front end using above and below functions but I would like to do this in the edit script.

Sample data laid out below and the expected result.

rowno	Product	flag	Value	Result
1	101	1	1	4
2	101	1	4	4
3	101	-1	8
4	101	1	10	10
5	101	1	2	10
6	101	1	3	10
7	101	-1	1
8	101	1	9	15
9	101	1	6	15
10	101	1	10	15
11	101	1	15	15

Re: Check each row value between flags and update the Value field

Saravanan_Desingh — Wed, 06 May 2020 13:21:35 GMT

The Result 1 is not 8?

Re: Check each row value between flags and update the Value field

quser0098 — Wed, 06 May 2020 13:25:50 GMT

Well, it should ignore -1 value but the range to compare should be from the first row until the row where the flag value is -1.

Re: Check each row value between flags and update the Value field

Saravanan_Desingh — Wed, 06 May 2020 19:52:09 GMT

Check this.

tab1: LOAD *, If(Previous(flag)=-1 Or IsNull(Peek(K1)), RangeSum(Peek(K1),1), Peek(K1)) As K1 ; LOAD * INLINE [ rowno, Product, flag, Value 1, 101, 1, 1 2, 101, 1, 4 3, 101, -1, 8 4, 101, 1, 10 5, 101, 1, 2 6, 101, 1, 3 7, 101, -1, 1 8, 101, 1, 9 9, 101, 1, 6 10, 101, 1, 10 11, 101, 1, 15 12, 101, -1, 9 ]; Left Join(tab1) LOAD K1, Max(Value) As Result Resident tab1 Group By K1;

Re: Check each row value between flags and update the Value field

Saravanan_Desingh — Wed, 06 May 2020 19:55:43 GMT

With correction.