How to remove consecutive values from data set

[sushil353]

Hi All,

I have come across one situation where i need to remove consecutive same values from the dataset.

In the below sample data.. for Key 1 Operation is Active on Sep-2012 and after that it has Active operation till Sep-2013.. So i need to Get the least Month Time Stamp.. Also After Oct-2013 It got Inactive and Then Again Jan-2014 and Feb-2014 it has Active operation consequtively.. In this case i need to get Jan-2014.. Same case is with Inactive..

if a Key is Active or Inactive more than once cosequtively then it should get least value..

Input Data:

Month Time Stamp	Key	Operation
Sep-2012	1	Active
Oct-2012	1	Active
Jan-2013	1	Active
Apr-2013	1	Active
May-2013	1	Active
Jul-2013	1	Active
Aug-2013	1	Active
Sep-2013	1	Active
Oct-2013	1	Inactive
Jan-2014	1	Active
Feb-2014	1	Active
Mar-2014	1	Inactive
Sep-2012	63	Active
Mar-2013	63	Inactive
Apr-2013	63	Active
May-2013	63	Active
Jun-2013	63	Inactive
Jul-2013	63	Inactive
Aug-2013	63	Active

Desired Output Data:

Month Time Stamp	Key	Operation
Sep-2012	1	Active
Oct-2013	1	Inactive
Jan-2014	1	Active
Mar-2014	1	Inactive
Sep-2012	63	Active
Mar-2013	63	Inactive
Apr-2013	63	Active
Jun-2013	63	Inactive
Aug-2013	63	Active

Thanks

Sushil

[maxgro · Accepted solution]

RESULT

SCRIPT

Source:

load

Stamp,

date(date#([Month Time], 'MMM-YYYY'), 'MMM-YYYY') as [Month Time], 

[Key Operation]

;

load * inline [

Month Time, Stamp, Key Operation

Sep-2012, 1, Active

Oct-2012, 1, Active

Jan-2013, 1, Active

Apr-2013, 1, Active

May-2013, 1, Active

Jul-2013, 1, Active

Aug-2013, 1, Active

Sep-2013 ,1, Active

Oct-2013, 1, Inactive

Jan-2014, 1, Active

Feb-2014, 1, Active

Mar-2014, 1, Inactive

Sep-2012, 63, Active

Mar-2013, 63, Inactive

Apr-2013, 63, Active

May-2013, 63, Active

Jun-2013, 63, Inactive

Jul-2013, 63, Inactive

Aug-2013, 63, Active

];

Table:

NoConcatenate load *

resident Source

where peek([Key Operation])<>[Key Operation]  or peek(Stamp)<>Stamp

order by Stamp, [Month Time];

drop table Source;