Find records greater than certain value based on a condition

safik003 — Sat, 16 Nov 2024 00:29:29 GMT

Dear All,

I have below records.

ID	SeqNo	Type	Value
100	0	10	4000
100	1	15	500
100	2	20	600
100	3	10	100
100	4	18	300
101	0	10	5000
101	1	20	450
101	2	15	600
101	3	18	250
101	4	16	380

For each ID, I want to flag those records which comes after Type=20 and flag them as 'Y'

For example, for ID=100, I want to take SeqNo 3 and 4 which comes after Type=20

i,e only take those records which is greater than SeqNo=2 (for Type=20, SeqNo=2)

SeqNo is always in increasing order and integer.

Below is the desired Output

ID	SeqNo	Type	Value	Flag
100	3	10	100	Y
100	4	18	300	Y
101	2	15	600	Y
101	3	18	250	Y
101	4	16	380	Y

A sample file is attached.

Re: Find records greater than certain value based on a condition

tresB — Fri, 12 Jun 2020 09:18:08 GMT

Try like:

Test: LOAD * INLINE [ ID, SeqNo,Type, Value 100, 0, 10, 4000 100, 1, 15, 500 100, 2, 20, 600 100, 3, 10, 100 100, 4, 18, 300 101, 0, 10, 5000 101, 1, 20, 450 101, 2, 15, 600 101, 3, 18, 250 101, 4, 16, 380 ]; t1: Load ID, SeqNo, If(ID=peek('ID') and (peek('Type')=20 or Peek(Flag)='Y'), 'Y', 'N') as Flag, Type, Value Resident Test Order by ID, SeqNo; NoConcatenate t2: Load * resident t1 Where Flag='Y'; Drop table Test, t1;

topic Re: Find records greater than certain value based on a condition in QlikView

Find records greater than certain value based on a condition

Re: Find records greater than certain value based on a condition