topic Re: Issue in calculating Turn for multiple dates in QlikView

Issue in calculating Turn for multiple dates

Tue, 30 Jun 2015 10:18:32 GMT

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

Re: Issue in calculating Turn for multiple dates

Gysbert_Wassenaar — Tue, 30 Jun 2015 11:45:16 GMT

Please post a qlikview document that demonstrates the problem.

Re: Issue in calculating Turn for multiple dates

Tue, 30 Jun 2015 12:03:32 GMT

Hi,

Here I attached the sample source data and QV app.

Re: Issue in calculating Turn for multiple dates

Tue, 30 Jun 2015 12:11:32 GMT

Can anyone help me out in this...

Re: Issue in calculating Turn for multiple dates

Tue, 30 Jun 2015 12:19:40 GMT

Create a straight table, not a text box, and enter an expression along the lines as the ones above, Sum(End - Start)

Re: Issue in calculating Turn for multiple dates

Tue, 30 Jun 2015 12:23:47 GMT

Hi Michael,

Thanks for your reply.

But that is not working, however I need the turn for every iteration it travels that phases.

--Sindhu.

Re: Issue in calculating Turn for multiple dates

Tue, 30 Jun 2015 12:30:13 GMT

Try something like this: Sum({1<[OrderNumber]=>} [End-Start]))

Re: Issue in calculating Turn for multiple dates

Gysbert_Wassenaar — Tue, 30 Jun 2015 12:32:06 GMT

What you want is not possible. There is not enough information to determine which startdate belongs to which enddate.

Re: Issue in calculating Turn for multiple dates

Tue, 30 Jun 2015 12:35:29 GMT

Exactly, when I load it it is doing n*n mappping.

But the right combinations are the ones which I posted in the Sample data.

I want to align it in this way and find the turn for it.

So I wanted some logic in making this work and avoid cross joins.

Re: Issue in calculating Turn for multiple dates

sasiparupudi1 — Tue, 30 Jun 2015 12:38:31 GMT

It is not possible. You need to come up with some sort of a unique key between the two tables other wise you end up having a cross join

Re: Issue in calculating Turn for multiple dates

Gysbert_Wassenaar — Tue, 30 Jun 2015 12:46:37 GMT

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.

Re: Issue in calculating Turn for multiple dates

Tue, 30 Jun 2015 13:05:35 GMT

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.

Re: Issue in calculating Turn for multiple dates

rubenmarin — Tue, 30 Jun 2015 13:08:44 GMT

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.

Re: Issue in calculating Turn for multiple dates

rubenmarin — Tue, 30 Jun 2015 13:10:58 GMT

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.

Re: Issue in calculating Turn for multiple dates

Tue, 30 Jun 2015 13:18:47 GMT

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.

Re: Issue in calculating Turn for multiple dates

Wed, 01 Jul 2015 12:39:38 GMT

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

Re: Issue in calculating Turn for multiple dates

rubenmarin — Wed, 01 Jul 2015 12:50:08 GMT

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.