Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Announcements
Save the Date: QlikWorld Online, June 24-25, 2020. Free global virtual event for data integration and data analytic gurus. Register Today
Highlighted
New Contributor

Lapse Time Between Records by User Inner Join Issue

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

Labels (1)