Do not input private or sensitive data. View Qlik Privacy & Cookie Policy.
Skip to main content

Announcements
Qlik Open Lakehouse is Now Generally Available! Discover the key highlights and partner resources here.
cancel
Showing results for 
Search instead for 
Did you mean: 
Anonymous
Not applicable

Time interval between start and end date

Hi,

I need help with a script that is already doing my head in. I have these 3 fields associated to an ID in a table.

[Receive Date]

[Follow up Date]

[Closed Date]

All the ID,s have Receive date and closed date but only few Id's have follow up date. I want to pull only those Id's which doesn't have a followup date but are closed less than 48-72hrs time period.

For Example:

ID1 has received date as 01/01/2018 and closed date as 01/03/2018

ID2 has received date as 01/01/2018 and closed date as 01/07/2018 and follow up date as 01/03/2018.


So the result should be only ID1 because it doesn't have a followup date and has been closed within 48hrs-72hrs.

Thanks.

1 Solution

Accepted Solutions
vishsaggi
Champion III
Champion III

Not sure if this is a good way but you can try like this. You can change the dates in the Inline data set for testing.

DateTimeTest:
LOAD *, IF(Len(Trim(FollowUpDate)) = 0 AND (IntDiff >= 48 AND IntDiff <= 72), ID) AS QualifiedIDs;
LOAD *, Num(Num#(Interval(ClosedDate - ReceivedDate, 'hh'),'##')) AS IntDiff INLINE [
ID, ReceivedDate, FollowUpDate, ClosedDate
ID1, 01/01/2018, , 01/03/2018
ID2, 01/01/2018, 01/03/2018, 01/07/2018
ID3, 01/01/2018, 01/03/2018, 01/03/2018
ID4, 01/02/2018, , 01/02/2018
ID4, 01/02/2018, , 01/05/2018
]
;

Your QualifiedIds Field will have the valid fields that matches your criteria.

View solution in original post

2 Replies
vishsaggi
Champion III
Champion III

Not sure if this is a good way but you can try like this. You can change the dates in the Inline data set for testing.

DateTimeTest:
LOAD *, IF(Len(Trim(FollowUpDate)) = 0 AND (IntDiff >= 48 AND IntDiff <= 72), ID) AS QualifiedIDs;
LOAD *, Num(Num#(Interval(ClosedDate - ReceivedDate, 'hh'),'##')) AS IntDiff INLINE [
ID, ReceivedDate, FollowUpDate, ClosedDate
ID1, 01/01/2018, , 01/03/2018
ID2, 01/01/2018, 01/03/2018, 01/07/2018
ID3, 01/01/2018, 01/03/2018, 01/03/2018
ID4, 01/02/2018, , 01/02/2018
ID4, 01/02/2018, , 01/05/2018
]
;

Your QualifiedIds Field will have the valid fields that matches your criteria.

Anonymous
Not applicable
Author

Thank You Vishwarath,

Will try this and let you know