Find records greater than certain value based on a condition

[safik003]

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.

[tresesco · Accepted solution]

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;