Filter a table based on the first chronological event

[#1]

Hello,

I have the following table:

EventTable:
Load * inline
[
ID1,    ID2, ID3,  Action,    EventDateTime
10,      1,    99,    Create,    '12/30/2016 11:02'
10,      1,    99,    Update,     '12/30/2016 11:03'
10,     1,     99,   Delete,     '13/30/2016 11:02'
10,      1,    98,   Delete,    '12/30/2016 14:02'
10,      1,    98,   Update,   '12/30/2016 12:02'
10,      2,    99,   Update,   '13/30/2016 13:02'
10,      2,    99,   Update,   '13/30/2016 15:02'
10,      2,    99,   Update,   '12/30/2016 08:02'
];

I would like to keep only one line for a given ID1/ID2/ID3 item i.e. the one with the Min (EventDateTime)

10,      1,    99,   Create,    '12/30/2016 11:02'

10,      1,    98,   Update,   '12/30/2016 12:02'

10,      2,    99,   Update,   '12/30/2016 08:02'

I would very much appreciate your help!

Thanks

Annick

[sunny_talwar · Accepted solution]

Try this:

EventTable:

Load * inline

[

ID1,    ID2, ID3,  Action,    EventDateTime

10,      1,    99,    Create,    '12/30/2016 11:02'

10,      1,    99,    Update,    '12/30/2016 11:03'

10,    1,    99,  Delete,    '13/30/2016 11:02'

10,      1,    98,  Delete,    '12/30/2016 14:02'

10,      1,    98,  Update,  '12/30/2016 12:02'

10,      2,    99,  Update,  '13/30/2016 13:02'

10,      2,    99,  Update,  '13/30/2016 15:02'

10,      2,    99,  Update,  '12/30/2016 08:02'

];

Right Join (EventTable)

LOAD ID1,

  ID2,

  ID3,

  Min(EventDateTime) as EventDateTime

Resident EventTable

Group By ID1, ID2, ID3;

[#3]

Thanks a lot Sunny for your quick reply!

I tried it, but I might have missed something, as this is the result I get:

For some reason I have lost the EventDateTime and the Action...

Any idea on how to solve this?

Many thanks. Annick

[sunny_talwar]

What is the exact script you used?

[#5]

Hello,

Here's my exact script:

EventTable:
Load * inline
[
ID1,     ID2, ID3,   Action,    EventDateTime
10,      1,   99,    Create,    '12/30/2016 11:02'
10,      1,   99,    Update,    '12/30/2016 11:03'
10,      1,   99,    Delete,    '13/30/2016 11:02'
10,      1,   98,    Delete,    '12/30/2016 14:02'
10,      1,   98,    Update,    '12/30/2016 12:02'
10,      2,   99,    Update,    '13/30/2016 13:02'
10,      2,   99,    Update,    '13/30/2016 15:02'
10,      2,   99,    Update,    '12/30/2016 08:02'
];

Right Join (EventTable)
LOAD ID1,
     ID2,
     ID3,
     Min(EventDateTime) as EventDateTime
Resident EventTable
Group By ID1, ID2, ID3;

Thanks a lot!

Annick

[#6]

Hi Sunny,

Sorry, please ignore my last question. Your initial reply is correct and works fine (The issue I encountered was due to the format of the date). Many thanks for your help. Annick