Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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;
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;