Calculating the days between two dates in the same table but different 'rows'

[#1]

Hello QlikCommunity,

i have already found solutions for a lot of problems i had here but now i'm stuck and hope you can help me.

What I'm trying to do is subtract two dates and create a new field with the result.

e.g. DateCreated - DateDelivered = WaitingTime

So far this is nothing special and could easily be solved in the LOAD script.

My problem is "DateDelivered" has to come from a different DataSet (Row) in the same Table.

Please see this example for better understanding:

PartID	ProcessStepID	ProcessStep	BatchID	DateCreated	DateDelivered	WaitingTime
1	1	A	11	01.01.2012	17.01.2012	-
1	2	B	11	20.02.2012	22.02.2012	34
1	3	C	11	03.03.2012	06.03.2012	9
1	4	D	11	07.03.2012	10.03.2012	1

WaitingTime should be calculated by taking "DateDelivered" of the current DataSet (e.g. PartID = 1; BatchID = 11; ProcessStepID = 2) and subtract it from "DateCreated" of the next DataSet (e.g. PartID = 1; BatchID = 11; ProcessStepID = 3) in the same table.

I want to get the waiting/idle time between one finished step until the beginning of the other.

WaitingTime = DateCreated(ProcessStepIP=3) - DateDelivered (ProcessStepID=2)

I'm really frustated as i have tried to find a solution for about 2 days now. I'm pretty new to QV and am really hoping for you guys to help me 🙂

Thank you very much in advance!

[Gysbert_Wassenaar · Accepted solution]

Try

Load

...

If(previous(BatchID)=BatchID and previous(PartID)=PartID, DateCreated-previous(DateDelivered)) as WaitingTime

...

from ...somewhere...

Depending on your source data you may need to use the date# function on the dates to create numeric dates that can be subtracted from each other.

[swuehl]

Maybe like this

RESULT:

LOAD

     PartID,

     ProcessStepID,

     ProcessStep,

     BatchID,

     DateCreated,

     DateDelivered,

     if(peek(PartID) = PartID, DateCreated-peek(DateDelivered)) as WaitingTime

RESIDENT TABLE

ORDER BY PartID, ProcessStepID;

drop table TABLE;

Assuming you alread loaded TABLE with above fields.

[#4]

That didn't work 😞

I should also mention that the Data is not sorted like in my example. I need to find the Row with the same BatchID and the same PartID but with the ProcessStepID of a previous ProcessStep.

Your example did calculate something but not in the correct way

PartID appears numberous times but with a different BatchID etc.

[#5]

thank you!!

After i set all my filters in the listboxes i get the correct result!

I just added one more thing:

If(previous(BatchID)=BatchID and previous(PartID)=PartID and previous(ProcessStepID)=(ProcessStepID-1), DateCreated-previous(DateDelivered)) as WaitingTime

[Gysbert_Wassenaar]

You can first load your data and then use a resident load to sort it and calculate the waiting time:

Temp:

LOAD

    PartID,

    ProcessStepID,

    ProcessStep,

    BatchID,

    DateCreated,

    DateDelivered

FROM yoursourcehere;

Result:

Load

    PartID,

    ProcessStepID,

    ProcessStep,

    BatchID,

    DateCreated,

    DateDelivered

    If(previous(BatchID)=BatchID and previous(PartID)=PartID, DateCreated-previous(DateDelivered)) as WaitingTime

Resident Temp

order by PartID, BatchID,ProcessStepID;

Drop table Temp;