Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements
Leverage your QlikView investment to modernize BI – see how! Join Group
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Creator II
Creator II

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

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 

Labels (2)
4 Replies
Highlighted
Creator II
Creator II

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

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.

Highlighted
MVP
MVP

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

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 🙂

 

Highlighted
Creator II
Creator II

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

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

Highlighted
Digital Support
Digital Support

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

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

To help users find verified answers, please do not forget to use the "Accept as Solution" button on any post(s) that helped you resolve your problem or question.
I now work a compressed schedule, Tuesday, Wednesday and Thursday, so those will be the days I will reply to any follow-up posts.