Issue in calculating Turn for multiple dates

[#1]

Hi Friends,

I've an issue in calculating the turn for two fields.

Lets say I have a field Date1, Date2 where the turn should be calucated for Date1 and Date2

Sample Data

Order No	Iteration	Start	End	Turn
123	Iteration1	23/06/2015 15:42:46	24/06/2015 09:11:33	=networkdays(23/06/2015,24/06/2015) =2
123	Iteration 2	24/06/2015 09:18:23	24/06/2015 18:29:17	--Turn 2 =1
123	Iteratin 3	26/06/2015 11:28:08	26/06/2015 14:08:18	--Turn 3 =1
123	Iteration 4	26/06/2015 14:36:12	26/06/2015 16:35:54	--Turn 4 =1
		Average		=Average(2,1,1,1)=1.25

But the Start Date and End Date comes from different tables based on Primary Key and iteration is what I added for better understanding of my requirement.

Now i want to calculate the turn at every iteration based on the Order No.

If I try to join these two fields with the Order No, it is doing cross join and populating 16 combinations and the turn metric is being calculated wrongly.

Wanted help on how to deal with such scenarios.

Final I want to see turn as 1.25 for the Order 123

Thanks,

Sindhu

[Gysbert_Wassenaar]

You must define the logic to do the mapping. Qlikview cannot. And neither can I. I don't know which records should map to which record. I have no information that allows me to determine the logic to do that.

[#3]

I tried generating a unique key with Order&Start&End combination, and when I join these dates but again ending up with cross joins.

If I have to create a logic with what I need to proceed.

Tried multiple scenarios, but failed.

Kindly help me in this scenario.

[rubenmarin · Accepted solution]

Hi Sindhu, the document attached returns the expected result but it has a very weak logic.

The first StartDate of an order is related to the first EndDate of that order, same for the 2nd, 3rd... For this sample of data is ok, but in real data I think you can find some issues.

[rubenmarin]

I forgot, according to the example in your first post, the excel you provided has the end times in the Start sheet and the start times in the End sheet.

[#6]

Hi Ruben,

Yes, the data I provided in excel is altered, actually the Start Sheet has the end dates and the End Sheet has the start dates.

But what I posted in the sample data in my first post is correct.

Trying to use your logic and check.

Many thanks,

Sindhu.

[#7]

Hi Ruben,

Your logic worked perfectly

Fortunately didn't find any issues.

Many Thanks.

The method what you suggested is similar to what I used, but only the using of peek is different. I used it in another way, but didn't give proper results.

Can you please help in understanding this statement, how does it work.

If(Peek(Order)=Order, Peek(Cont)+1, 1) as Cont

It will help me in understanding more technically.

Thanks a lot...

Regards,

Sindhu

[rubenmarin]

Hi Sindhu, as you don't know wich start is related to wich end I load them in sorted by : ORDER, DateTime (Start or End).

Using that sort, if the previous loaded Order is different from the current record it means is a new order, so the Cont starts at 1 (it goes for the 'else' part of the 'if).

If the Order is equal than the previous it takes the value Cont has in previous record and adds '1'.

This way Cont is used as the ID that relate each Start with his End: Start of Order x with Cont=1 will be related with the End of the same order and the same Cont.

Just to advise again that is a very weak logic, I don't know where the data came from but for example: there will be a Start, but if there is no End (because of a blackout, deleted record, duplicated start...), the next check for that order is again a Start...something like this will break all the logic.