Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
mccook
Partner - Creator
Partner - Creator

Joining Data on Different Lines

Hi,

I have a more complex version of data that looks like below:

DatePersonActionPlace
22/03/2019 13:41:36DeanStartHome
22/03/2019 13:55:22TimStopOffice
22/03/2019 14:01:21DeanStopHome
23/03/2019 10:10:10HelenStartShed
23/03/2019 10:50:01HelenStopCar Park
23/03/2019 11:15:01DeanStartOffice

 

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 DateStop DateDurationStatusPersonPlace
22/03/2019 13:41:3622/03/2019 14:01:2100:19:45ValidDeanHome
 22/03/2019 13:55:22 InvalidTimOffice
23/03/2019 10:10:10  InvalidHelenShed
 23/03/2019 10:50:01 InvalidHelenCar Park
23/03/2019 11:15:01  InvalidDeanOffice

 

Any advice appreciated,


Thanks,


Dean

Labels (1)
1 Solution

Accepted Solutions
sunny_talwar

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;

View solution in original post

2 Replies
sunny_talwar

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;
mccook
Partner - Creator
Partner - Creator
Author

Excellent thanks, a little change to match my actual data and a few other scenarios and it's working fine.