topic Re: Calculating the days between two dates in the same table but different 'rows' in QlikView

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

Thu, 22 Aug 2013 12:43:30 GMT

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!

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

Gysbert_Wassenaar — Thu, 22 Aug 2013 12:50:35 GMT

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.

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

swuehl — Thu, 22 Aug 2013 12:54:52 GMT

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.

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

Thu, 22 Aug 2013 13:15:05 GMT

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.

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

Thu, 22 Aug 2013 13:18:01 GMT

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

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

Gysbert_Wassenaar — Thu, 22 Aug 2013 13:49:55 GMT

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;