Sequence of Events based on Dates and Status

[spetushi]

Hello,

I am looking at transforming the data in columns A,B, and C to the results captured in columns E, F, G, and H.

The data in columns E,F and G is the same as A, B, and C, but with the rows of interest captured in black and the rows to be ignored in sequencing as gray. Column H is assigned a sequence number each time the Status value changes based on Timestamp and within each ID.  Attached is a CSV file with sample data as well for a better reference to this challenge question.

What will be the direction to go with this?

Thank you,

SP

[Anonymous]

Check with Prevuous() in load script like

if(STATUS<>previous(STATUS).......

[Anil_Babu_Samineni]

Might be this

FirstSortedValue(STATUS)

[spetushi]

Thanks Allu,

Something I was considering but I am not getting the right results

[spetushi]

Thanks for the reply! Will give it a shot ... probably will need a For loop and few transformation steps

[maxgro · Accepted solution]

PFA

[spetushi]

Thank you Massimo, this is exactly what I was looking for.

Could you please provide your comments on the following part of the script?

STATUS <> Peek('STATUS') as FLAG,

  if(ID <> Peek('ID'), 1,

  if(STATUS <> Peek('STATUS'), Peek('SEQ') +1, Peek('SEQ')

  )) as SEQ

[maxgro]

Directory;

S:

LOAD ID,

    STATUS,

    TIMESTAMP,

    rowno() as ROWNO

//    ID,

//    STATUS,

//    TIMESTAMP,

//    Seq#

FROM

[CSVFile_2016-10-12T21_22_02.csv]

(txt, codepage is 1252, embedded labels, delimiter is ',', msq);

S1:

NoConcatenate

Load

  *,

  STATUS <> Peek('STATUS') as FLAG,

  if(ID <> Peek('ID'), 1,

  if(STATUS <> Peek('STATUS'), Peek('SEQ') +1, Peek('SEQ')

  )) as SEQ

Resident S

Order By ROWNO;

DROP Table S;

S2: NoConcatenate

Load ID,

    STATUS,

    TIMESTAMP,

  if(FLAG, SEQ) as SEQ,

  FLAG,

  ROWNO

Resident S1;

DROP Table S1;

Peek to get the previously loaded row. When you use peek the order is important, this is why I added a ROWNO and I used ROWNO in order by.

Regarding the IF statement:

        when ID changes (ID <> ID previuos row)

    --> SEQ is 1

when ID doesn't change

    when STATUS changes (STATUS <> STATUS previous row)

              --> SEQ = SEQ +1

    when STATUS dosesn't change

              --> SEQ = SEQ of the previous row

I added 2 fields to the script (PFA) so you can see how peek works

Also you can read here

Peek() vs Previous() – When to Use Each

Missing Manual - Peek()

Peek() or Previous() ?

[spetushi]

Massimo, thank you for the detailed explanation!

This sets me in a good path to reach my next step of counting occurrences of specific events, e.g. New Entry to Data Entry event.

[DipeshVadgama]

Hi,

I have similar kind of problem with my data. But difference is timestamp and ID is not in order. I would like to know how can I apply similar solution but with proper order of timestamp and IDs.

Appreciate your help.