Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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!
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
Please post an example document with some source data and a table with the expected outcome.
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