Hello
I am working with dektop and I am trying to develop a chart that shows the time lapses between activities for individual employees. Example dataset would be below, in reality the data will come in via a spreadsheet and will not be ordered.
[DATASET]:
LOAD
ActionUser, ActionDateTime,PickedQty
INLINE
[
ActionUser, ActionDateTime ,PickedQty
PETE02 24/02/2020 01:00:36, 4
PETE02 26/03/2020 01:01:15, 1
PETE02 26/03/2020 01:01:46, 1
199345 26/03/2020 01:33:06, 1
199345 26/03/2020 01:34:06, 1
199345 26/03/2020 01:34:13, 2
];
[SAMPLEDATA]:
LOAD
ActionUser as [ActionUserSD],
PickedQty as [PickedQtySD],
ActionDateTime as [ActionDateTimeSD]
Resident DATASET
order by ActionDateTime asc;
TEST1:
LOAD ActionDateTimeSD, ActionUserSD,PickedQtySD,RowNo() as RN Resident SAMPLEDATA;
TEST2:
LOAD
ActionUserSD,
ActionDateTimeSD,
PickedQtySD,
Peek(ActionDateTimeSD) as LEAD
Resident TEST1
Order By RN DESC;
TEST3:
LOAD
ActionUserSD,
ActionDateTimeSD,
PickedQtySD,
Peek(ActionDateTimeSD) as LAG
Resident TEST1
Order By RN ASC;
NoConcatenate
Result:
LOAD *
Resident TEST2;
Inner Join
LOAD *
Resident TEST3;
This almost works but I have got something wrong with my Inner Join, it is mixing up the employees together. I just want the join for the lead and lag for individuals discreetly see screen shot. I have applied a filter to the Results table and just selected the users PETE02 but it is showing ActionUser 199345. I am also getting a sync error even though the application runs, is that todo with columns alias?
Any help would be appreciated
