Multiple rows in OTIF Calculation

[#1]

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!

[Gysbert_Wassenaar]

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

[#3 · Accepted solution]

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