Interval Time diffrences with Criteria

[Anonymous]

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!

[Digvijay_Singh]

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

[Anonymous]

Sure here ya go.

[Digvijay_Singh]

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?

[Digvijay_Singh · Accepted solution]

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,

DJ

[maniram23]

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;

[Anonymous]

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!