Qlik Community

Ask a Question

New to Qlik Sense

If you’re new to Qlik Sense, start with this Discussion Board and get up-to-speed quickly.

Announcements
Our May 2021 end-to-end product release from Data Integration to Data Analytics is out! READ DETAILS
cancel
Showing results for 
Search instead for 
Did you mean: 
GS500
Contributor
Contributor

Date Calculations within same field

Hello,

I need help with a load script.  I'm trying to calculate the difference between multiple time records with different Event IDs, for each PersonID.  I have a table like below.  EventID 1 = Start Time, EventID2 = End Time.  There can be multiple Start and End Times per date.

PersonIDDateTimeEventID
11/1/19 7:05 AM1
21/1/19 8:03 AM1
11/1/19 12:00 PM2
11/1/19 1:00 PM1
21/1/19 4:00 PM2
11/1/19 4:30 PM2


I need to find the total duration between the EventIDs 1 and 2 for each Person ID and Date like:

PersonIDDateDuration
11/1/20198:25:00
21/1/20197:57:00

 

I think I need to use some combination of Previous and Peek, but not sure exactly how to accomplish this.  Any help pointing me in the right direction would be appreciated.

Edit: Corrected dates in example table

1 Solution

Accepted Solutions
Channa
Specialist III
Specialist III

  If(PersonID = Previous(PersonID), Time(Previous (DateTime)-DateTime)) as Difference

Channa

View solution in original post

6 Replies
dwforest
Specialist II
Specialist II

Something like this:

LOAD
    PersonID,
    Date(Floor(DateTime)) AS Date,
    if(EventID=2, if(Previsou(PersonID)=PersonID and Previous(EventID)=1,Previous(DateTime) - DateTime)) as Duration
;
Select
    PersonID,
    DateTime,
    EventID
From (yourtable)
Order By PersonID, DateTime, EventID;
Channa
Specialist III
Specialist III

how ru calculating Duration 8:25

Channa
GS500
Contributor
Contributor
Author

I'm sorry, my example had error, all dates should have been 1/1/2019.  I have edited original post, please see example table again.  It is the total duration for the day 1/1/2019

7:05AM to 12:00PM = 4:55

1:00PM to 4:30PM = 3:30

4:55 + 3:30 = total duration of 8:25

Channa
Specialist III
Specialist III

  If(PersonID = Previous(PersonID), Time(Previous (DateTime)-DateTime)) as Difference

Channa

View solution in original post

GS500
Contributor
Contributor
Author

Thanks, I got it to work with a combination of both of your responses. 

However, I have found that there is an additional issue with my data that I need to solve for.  Sometimes the Start and End Times happen at the same second, so when I do the order by it doesn't always sort the way I need it to, with Start Time before End Time.  

How can I either make sure the sort always has Start Time before End Time or just exclude those events that happen simultaneously?  

 

Thanks

GS500
Contributor
Contributor
Author

I figured it out, just have to sort by DateTime Asc, EventID Desc.  

 

Thanks!