Qlik Community

New to Qlik Sense

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

Announcements
IMPORTANT security patches for GeoAnalytics Server available to download: READ DETAILS
cancel
Showing results for 
Search instead for 
Did you mean: 
segerchr
Contributor III
Contributor III

Need to count hours between dates in a while

Hey, i need to count hours between dates. The problem is there is a 1:n. So i have to use a while or something else. 
Could someone help how to do ?

I need the following:

There are a lot of ticketnumbers in the system. Example attached(1). I need to count all the pause times in hours. 

A Ticketnumber can have more that one pause in their lifetime. Now i have to search if there is ActivityAction 5. Use this CreationDate, go to the next event and use this as ClosedDate if this is not a 5 and try to get the duration between the two dates.  Is that possible ?

BR Christian

 

 

1 Solution

Accepted Solutions
marcus_malinow
Specialist III
Specialist III

Hi, no need for a loop

You could try something like this:

load
TicketNumber,
ActivityDesc,
ActivityAction,
Actcreate,
if(ActivityAction = 5 and peek('TicketNumber', -1) = TicketNumber, (peek('Actcreate', -1) - Actcreate) * 24) as PauseDurationHours
RESIDENT [your source table]
ORDER BY TicketNumber, Actcreate desc;

View solution in original post

2 Replies
marcus_malinow
Specialist III
Specialist III

Hi, no need for a loop

You could try something like this:

load
TicketNumber,
ActivityDesc,
ActivityAction,
Actcreate,
if(ActivityAction = 5 and peek('TicketNumber', -1) = TicketNumber, (peek('Actcreate', -1) - Actcreate) * 24) as PauseDurationHours
RESIDENT [your source table]
ORDER BY TicketNumber, Actcreate desc;

segerchr
Contributor III
Contributor III
Author

Thanks for helping. It works fine.