If you’re new to Qlik Sense, start with this Discussion Board and get up-to-speed quickly.
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.
|1||1/1/19 7:05 AM||1|
|2||1/1/19 8:03 AM||1|
|1||1/1/19 12:00 PM||2|
|1||1/1/19 1:00 PM||1|
|2||1/1/19 4:00 PM||2|
|1||1/1/19 4:30 PM||2|
I need to find the total duration between the EventIDs 1 and 2 for each Person ID and Date like:
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
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;
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
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?