How to pick only one TaskID if other records with same ID starts and ends in between

[pranaview]

Hi All,

Requirement: I have multiple tasks for a single RequestID and sometime same tasks can be repeated multiple times for the same RequestID with different Start DateTime and End DateTime.

What I want is to check the overlapping of tasks with same ID based on the Start/End Dates. For ex: TaskID 751 is being repeated 3 times for the same RequestID i.e. 2. 

RequestID        TaskID                      OpenDate                                     ClosedDate           

1                            750                   07/10/2019   9:20:22                  07/12/2019   9:20:22  

2                            751                   07/08/2019   15:40:22                07/15/2019   9:20:22  

2                            751                   07/10/2019   12:24:22                07/12/2019   17:50:22  

2                            751                   07/13/2019   16:24:22                07/15/2019    8:33:22  

Now if I do this record by record i will have 3 different Lead Times for TaskID 751 which will impact the final Lead time when i'll do the aggr. Since the second and third iteration of TaskID 751 got opened and closed during the time period of the first instance of the task so the time difference of only first instance should be considered and calculated.

I hope i have made the requirement clear enough, if not then please let me know.

I need to get this done quickly to move forward in the development, so any suggestions will be appreciated.

Thanks,
Pranav 

[lockematthewp]

i am not sure that I understand completely. but I think that you can use the min and max functions of each request id to determine the lead time.

[petter]

You should be able to do it like this:

DATA:
LOAD
  RequestID,
  Only(TaskID) AS TaskID,
  Date(Min(OpenDate),'MM/DD/YYYY hh:mm:ss') AS OpenDate,
  Date(Max(ClosedDate),'MM/DD/YYYY hh:mm:ss') AS ClosedDate
GROUP BY
  RequestID;
LOAD
  *,
  ClosedDate-OpenDate AS LeadTime
INLINE [
RequestID,TaskID,OpenDate,ClosedDate
1,750,07/10/2019 09:20:22,07/12/2019 09:20:22
2,751,07/08/2019 15:40:22,07/15/2019 09:20:22
2,751,07/10/2019 12:24:22,07/12/2019 17:50:22
2,751,07/13/2019 16:24:22,07/15/2019 08:33:22
];

Please click LIKE to give credit to my answer if you find it useful 🙂

[pranaview]

Hi,

I though of using similar kind of approach. I combined both RequestID and TaskID and used it in Group by clause. 

This will work as long as the Dates are overlapping. Now consider the below scenario 

RequestID   TaskID          OpenDate              ClosedDate 
1           750       07/10/2019 09:20:22      07/12/2019 09:20:22 
2           751       07/08/2019 15:40:22      07/15/2019 09:20:22
2           751       07/10/2019 12:24:22      07/14/2019 17:50:22 
2           751       07/20/2019 16:24:22      07/22/2019 08:33:22 

In this case, Min(OpenDate) for TaskID 751 would be 07/08/2019 15:40:22 and

Max(ClosedDate ) = 07/22/2019 08:33:22 

But third instance of TaskID 751 starts on 07/20/2019 whereas second instance ended on 07/12/2019.

This gap will also be included while calculating the Lead time.

Regards,

Pranav

[Brett_Bleess]

Only thing I can add here are some links that might give you some further ideas:

https://community.qlik.com/t5/Qlik-Design-Blog/Buckets/ba-p/1463216

https://community.qlik.com/t5/Qlik-Design-Blog/IntervalMatch/ba-p/1464547

You can search the Design Blog area for further items, but I thought these two might be in play here.  If you came up with something else, please post and mark that as the solution using the Accept as Solution button.

Regards,
Brett