Flag the last common value in a field before it changes

[regowins]

Hi,

I  have a table as below and I need to create a flag / Indicator (Y) field showing the last common value before it changes to a new value.

For example, I need a 'Y' in the indicator field for the last common value of Gate_No before it changes the next value.

Thanks for the Help!

SERIAL	GATE_NO	LAST_indicator
1011	1	N
1011	1	N
1011	1	Y
1011	2	N
1011	2	N
1011	2	N
1011	2	Y
1011	3	N
1011	3	N
1011	3	N
1011	3	Y
1011	4	N

[senpradip007]

Try somthing like :

If(GATE_NO= Below(GATE_NO), 'N', 'Y')

or

Load SERIAL,

          GATE_NO,

          If(GATE_NO=Peek(GATE_NO), 'N', 'Y') AS LAST_indicator

From <Source File>;

[regowins]

Thanks for the response. I am not sure if the below() function works in the script. The Peek()  will put the 'Y' on the FIRST new Gate NO value and I want the 'Y' on the LAST Gate No Value.

[#4]

Have you tried using previous()?

[jerem1234 · Accepted solution]

You can try this script:

Data:

Load * Inline [

SERIAL,GATE_NO

1011,1

1011,1

1011,1

1011,2

1011,2

1011,2

1011,2

1011,3

1011,3

1011,3

1011,3

1011,4

];

Temp2:

NoConcatenate Load *,

rowno() as ROW_NO

Resident Data;

Temp3:

NoConcatenate Load *,

if(GATE_NO = Previous(GATE_NO), 'N', 'Y') as LAST_indicator

Resident Temp2

Order by SERIAL, GATE_NO, ROW_NO DESC;

FINAL:

NoConcatenate Load *

Resident Temp3

Order by SERIAL, GATE_NO, ROW_NO ASC;

Drop Tables Data, Temp2, Temp3;

//Drop field ROW_NO From FINAL;

If you don't want the ROW_NO field, just un-comment the last line of the code.

Please find attached.

Hope this helps!

[regowins]

I think Previous() would give me the same result as Peek(). I need something that looks 1 record forward to see if  the Gate No is not the same then write 'Y' at the current record else 'N'.

[regowins]

Thanks! I did not think of using Order by DESC