topic Multiple rows in OTIF Calculation in QlikView

Multiple rows in OTIF Calculation

Fri, 04 Oct 2013 06:57:09 GMT

Hi!

I been trying to figure out a good way to solve my problem but I keep getting stuck.

I'm currently working on a OTD calculation doc. However, one requirement is to be able to calculate the OTIF (On Time in Full) as well.

We have a order, and order position.

In the OTD calculation we measure the status on order position level, and this works great.

The OTIF calculation will be on order level and this causes problem.

With help of max(date) I get the latest position(s) sent, and I load the status of that specific position. Tho, sometimes there is more than one position causing problems that might have a different status (or none at all for that matter) and this gives me multiple rows.

OrderNo Status Cause

001 OnTime -

123 Late Out_of_stock

890 Late Out_of_stock

Late Customer_related

Late -

As you can see, order 123 is fine. However, 890 contain multiple rows due to the fact that the order have more than one position sent at max(date) and different status on different positions.

How can I prioritize or exclude those multiple rows? Is there a way to "look in the future" during the load process?

Thank you very much!

Re: Multiple rows in OTIF Calculation

Gysbert_Wassenaar — Fri, 04 Oct 2013 08:48:51 GMT

Please post an example document with some source data and a table with the expected outcome.

Re: Multiple rows in OTIF Calculation

Fri, 04 Oct 2013 08:56:53 GMT

Thank you for shown interest.

Believe it or not, I manage to solve it now by adding a lot of different logic in my loading phrases.

With a couple of different left joins with different "where" cases and some thinking I manage to get the right output:

Before:

OrderNo Status Cause

001 OnTime -

123 Late Out_of_stock

890 Late Out_of_stock

Late Customer_related

Late -

Now after:

OrderNo Status Cause

001 OnTime -

123 Late Out_of_stock

890 Late Out_of_stock