Weekly Challenges

T_Willins · ‎09-03-2019

I did not complete this in my under 10 minute goal. I found myself getting tripped up on looking at the wrong field for updating the "Order Complete" column. Attached is my solution.

Spoiler

phottovy · ‎09-03-2019

I'm not sure my solution would catch multiple shipments but it works for the data provided.

Bdonahue · ‎09-03-2019

Spoiler

Hopefully most systems have an order/invoice table to reference instead of just an open/shipped table.

1.)Replace "|" with ',' split to columns, dynamic rename based upon first row.

2.) Join and full-outer union.

3.)Summarize to Order/Line level, determine line level shipment and flag for complete or not complete.

4.) Summarize on Order, count number of open lines and set flag for Completed/Not completed orders.

5.) Join back to Line level

6.) Join back to shipment level

7.) Filter on Shipment number != to Null

8.) Sort on Order Id and Line Id

Screen Shot 2019-09-03 at 4.33.33 PM.png

Screen Shot 2019-09-03 at 4.33.33 PM.png

Hopefully most systems have an order/invoice table to reference instead of just an open/shipped table. 1.)Replace "|" with ',' split to columns, dynamic rename based upon first row. 2.) Join and full-outer union. 3.)Summarize to Order/Line level, determine line level shipment and flag for complete or not complete. 4.) Summarize on Order, count number of open lines and set flag for Completed/Not completed orders.5.) Join back to Line level6.) Join back to shipment level 7.) Filter on Shipment number != to Null8.) Sort on Order Id and Line Id

lmorrell · ‎09-03-2019

As others have noted, Order 8 Line 12 is a bit dodgy due to the nature of having a quantity of 1 being in Open Orders and a quantity of 1 having been shipped... Regardless, my answer matches the output and is attached.

Spoiler