topic Re: Interval Time diffrences with Criteria in QlikView

Interval Time diffrences with Criteria

Anonymous — Wed, 25 Nov 2020 16:16:04 GMT

Hi All,

I am trying to determine time differences between sent dates and flag the earliest time difference for a "root task". The difference between the first two sent dates, that meet the criteria would be the initial response time, I would also want to sum all differences to get a total time for a "root task".

I have included a snippet of the table structure below: I would want to get the difference of the sent dates for TaskID 5868632 and 58817076 for the initial response time. I would want to then sum all other differences in sent date where TaskID are different. I would then want to group by RootTaskID

I have tried several different ways to get this with Peek and Previous. Currently I am using the code below, but the flag is not identifying correctly and I am not getting the time differences.

I know there have been several post regarding this topic, but I have not sound one that I have been able to leverage. Thank you for your help in advance!

Re: Interval Time diffrences with Criteria

Digvijay_Singh — Thu, 23 Jun 2016 16:52:23 GMT

Can you attach excel or smthg with sample data, I think the image shared cannot be used directly.

Re: Interval Time diffrences with Criteria

Anonymous — Thu, 23 Jun 2016 17:00:08 GMT

Sure here ya go.

Re: Interval Time diffrences with Criteria

Digvijay_Singh — Fri, 24 Jun 2016 04:43:57 GMT

For one task id you have several sentdate, which one you need to choose to compare(get difference) it with other task id's sent date?

Re: Interval Time diffrences with Criteria

Digvijay_Singh — Fri, 24 Jun 2016 05:14:55 GMT

Your script needed improvement, I still don't understand the full requirement but this below script calculates the time difference of sentdate between difference task id -

Task:

LOAD RootTaskID,

TaskID,

originaltaskid,

RecipientID,

sentdate as SentDate,

StatusID

FROM

Example.xlsx

(ooxml, embedded labels, table is Sheet3);

Task2:

Load

TaskID,

RootTaskID,

SentDate,

If(Previous(TaskID)<>TaskID and Previous(RootTaskID)=RootTaskID,Interval(SentDate-Peek(SentDate),'hh:mm:ss'),Null()) as ResponseTime

Resident Task

Order By RootTaskID,SentDate;

Drop table Task;

Can you explain the logic of initialFlag?

Regards,

Re: Interval Time diffrences with Criteria

maniram23 — Fri, 24 Jun 2016 05:48:37 GMT

Hi ,

can you please try this one

Task:

LOAD RootTaskID,

TaskID,

originaltaskid,

RecipientID,

sentdate as SentDate,

StatusID

FROM

Example.xlsx

(ooxml, embedded labels, table is Sheet1);

Task2:

Load

TaskID,

RootTaskID,

SentDate,

If(Previous(TaskID)<>TaskID and Previous(RootTaskID)=RootTaskID,Interval(SentDate-Peek(SentDate),' D hh:mm:ss'),Null()) as ResponseTime

Resident Task

Order By RootTaskID,SentDate;

Drop table Task;

Re: Interval Time diffrences with Criteria

Anonymous — Fri, 24 Jun 2016 14:30:59 GMT

Hi DJ,

The data was structured in a less than optimal way, so it makes things a little more complicated than it should be. It may help to think of the data this way- A roottask is a string of questions and responses about an incident. Each taskid is a different communication from a sender. The first sentdate in chronological order would be the initial question, the second sentdate would be the response to that question. The second sentdate would have to have a different taskid, but have the same roottask. I want to get the time difference here to see how long it takes to respond. There are multiple identical taskids because there are group sends. The last time diffrence I would want to get is the difference from the first sentdate to last sentdate, how long a roottask takes to close.

Hopefully that was not too confusing. Thank you for the help with this!