Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi,
I have a more complex version of data that looks like below:
Date | Person | Action | Place |
22/03/2019 13:41:36 | Dean | Start | Home |
22/03/2019 13:55:22 | Tim | Stop | Office |
22/03/2019 14:01:21 | Dean | Stop | Home |
23/03/2019 10:10:10 | Helen | Start | Shed |
23/03/2019 10:50:01 | Helen | Stop | Car Park |
23/03/2019 11:15:01 | Dean | Start | Office |
If there is a start and a stop for the same person at the same place, then I want to record the length of time, but if there is only a start or only a stop, or they start and stop at a different place, I want to mark as invalid, so the result I want is something like below:
Start Date | Stop Date | Duration | Status | Person | Place |
22/03/2019 13:41:36 | 22/03/2019 14:01:21 | 00:19:45 | Valid | Dean | Home |
22/03/2019 13:55:22 | Invalid | Tim | Office | ||
23/03/2019 10:10:10 | Invalid | Helen | Shed | ||
23/03/2019 10:50:01 | Invalid | Helen | Car Park | ||
23/03/2019 11:15:01 | Invalid | Dean | Office |
Any advice appreciated,
Thanks,
Dean
You can try something like this
Table:
LOAD Date as TimeStamp,
Date(Floor(Date)) as Date,
Person,
Action,
Place;
LOAD * INLINE [
Date, Person, Action, Place
22/03/2019 13:41:36, Dean, Start, Home
22/03/2019 13:55:22, Tim, Stop, Office
22/03/2019 14:01:21, Dean, Stop, Home
23/03/2019 10:10:10, Helen, Start, Shed
23/03/2019 10:50:01, Helen, Stop, Car Park
23/03/2019 11:15:01, Dean, Start, Office
];
tmpTable:
LOAD TimeStamp as [Start],
Date,
Person,
Place
Resident Table
Where Action = 'Start';
Join (tmpTable)
LOAD TimeStamp as [Stop],
Date,
Person,
Place
Resident Table
Where Action = 'Stop';
FinalTable:
LOAD Start,
Stop,
Interval(Stop - Start, 'hh:mm:ss') as Duration,
If(IsNull(Stop - Start), 'Invalid', 'Valid') as Status,
Date,
Person,
Place
Resident tmpTable;
DROP Tables Table, tmpTable;
You can try something like this
Table:
LOAD Date as TimeStamp,
Date(Floor(Date)) as Date,
Person,
Action,
Place;
LOAD * INLINE [
Date, Person, Action, Place
22/03/2019 13:41:36, Dean, Start, Home
22/03/2019 13:55:22, Tim, Stop, Office
22/03/2019 14:01:21, Dean, Stop, Home
23/03/2019 10:10:10, Helen, Start, Shed
23/03/2019 10:50:01, Helen, Stop, Car Park
23/03/2019 11:15:01, Dean, Start, Office
];
tmpTable:
LOAD TimeStamp as [Start],
Date,
Person,
Place
Resident Table
Where Action = 'Start';
Join (tmpTable)
LOAD TimeStamp as [Stop],
Date,
Person,
Place
Resident Table
Where Action = 'Stop';
FinalTable:
LOAD Start,
Stop,
Interval(Stop - Start, 'hh:mm:ss') as Duration,
If(IsNull(Stop - Start), 'Invalid', 'Valid') as Status,
Date,
Person,
Place
Resident tmpTable;
DROP Tables Table, tmpTable;