Challenge #179: Calculating Order Status

A solution to last week's challenge can be found here!

This week's challenge comes to us from @wwatson! Based on a use case, this challenge recreates a real-life business problem (calculating order status) that was solved with Alteryx.

Thanks @wwatson for submitting this challenge!

Have a great idea for a challenge? We want to hear it! Submit your challenge idea to our Academy Team to contribute to our Community.

challenge_179_start_file.yxmd

challenge_179_solution.yxmd

Developer

Parse

Join

Preparation

Advanced

Data Analysis

Intermediate

Accepted answers

All comments

RolandSchubert

My solution.

Spoiler

challenge_179_solution_rsc.yxmd

acastelazo

There might be an error with order #8 line #12. The solution has it marked as "Partial" for the "Line Complete Status," but I argue that line is in fact complete. Just because a line is in the "Open Orders" file doesn't mean that line is partially shipped. It just means that line belongs to an order that is still open. Therefore, you have to check the shipped quantities against the ordered quantities to check the status of each line.

Spoiler

challenge_179_acastelazo.yxmd

andresleonm

I was about to say that. It has to say "Order Complete Status" -> "Partial" and "Line Complete Status" -> "Line Complete".

patrick_digan

Here's my not very concise solution:

Spoiler

challenge_179_start_file.yxmd

RolandSchubert

I think, "Open Orders" lists the remaining (not yet delivered) quantity, so a line "8|12|2,H,1" in "Shipments" will be needed to fulfill the order line.

cplewis90

This was a tough one as I was able to figure out the Order Complete Status relatively quickly, but the Line Complete Status was tough and had some tricky parts. Overall fun challenge! I look forward to seeing how others finish this one.

Spoiler

challenge_179_CL.yxmd

hbraunius

My solution attached.

challenge_179_solution_hb.yxmd

andresleonm

Here is my solution. Please fix the challenge output in "Order Number" -> 8 "Line Number" -> 12.

Spoiler

challenge_179_start_file.yxmd

cgoodman

I spend longer than I'd like to admit solving this as I didn't understand why "8|12,H,1" isn't complete / complete, but worked out in the end how to get it to match the solution.

Spoiler

challenge_179_solution.yxmd

T_Willins

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

challenge_179_solution.yxmd

phottovy

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

challenge_179_ph_solution.yxmd

Bdonahue

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

lmorrell

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

challenge_179_LM.yxmd

RWvanLeeuwen

There's a flaw in one of the orders in the expected outcome - at least, I think so.

Spoiler

challenge_179_start_file - RWvL.yxmd

pasccout

Not my cleanest solution but it works... I get to the same results...

Not sure about the quantity on order line 8 line 12 though...

challenge_179_my_solution.yxmd

sudhar46

Not the best solution but still got the output.

179 Screenshot Output.bmp

DanielMS

Spoiler

Dan_Salway_challenge_179.yxmd

JGreenSCVL

I would agree with a few of the other replies that there might be an error in the input file for one of the line items.

challenge_179_Solution_1.yxmd

NicoleJohnson

My solution!

Spoiler

Cheers!

challenge_179_NicoleJohnson.yxmd

mi_tob

My solution:

Spoiler

Cheers,
Michael

challenge_179_results_file.yxmd

lchavezv

My solution

challenge_179_start_file_solved_lchavez.yxmd

Adam_Dooley

Here it is

Spoiler

challenge_179_AD.yxmd

anuagr

Solution is attached

challenge_179_solution_file.yxmd

Kwame

Still new to alteryx but this is how I went about the challenger. I am very sure I can tune it to make it better.

challenge_179_start_file.yxmd

jarjarbingie

Spoiler

Challenge 179 - jarjarbingie.yxzp

nikolaskardasis

Spoiler

challenge_179_solution.yxmd

nikolaskardasis

Spoiler

challenge_179_solution.yxmd

mikkel

Spoiler

Fun challenge!

Weekly Challenge #179_Mikkel.yxmd

DavidThorpe

My solution:

challenge_179_solution_DT.yxmd

Original_Yodies

Probably not the most elegant or efficient, but here is my solution.

Looking forward to seeing what others of you have done now!

Spoiler

challenge_179_Yodies.yxmd

wdavis

Took me a while to understand which shipments were completed. The best use of the 3 join outputs!

Challenge 179.yxmd

Reesetrain2

All,

An interesting challenge as it too me a bit to realize that this one was two parts:

Spoiler

The first part was to Sum the open order numbers and get the status of the Open order and combine those two pieces of information later in the workflow.

-I used Join and Union twice and the coded the results in an Active order via the Formula Tool.

-Cheers!

Matt

Challenge_179_Reesetrain_Submission.yxmd

kyseq

My solution is attached. I really thought I could do the bulk of the work with multi-formula tool, but I just couldn't make it work any better than the standard tool. I feel multi-formula is really powerful, but I can't quite get a grasp it. So I just used a combination of the standard formula tools and the summarize tool to make it work.

challenge_179_solution_kq.yxmd

wwatson

The way i designed it the fact that an order line exists in the open order means it is still open, if it is also in the shipped file then it is by definition a partial shipment.

Erin

Complete, but have a different answer around order #8.

Spoiler

For Order #8 part H, customer ordered 1 and 1 was shipped - so the Line Complete Status should say 'Line Complete' in my humble opinion vs 'Partial' as shown in the solution.

challenge_179_submission_EM.yxzp

Quick Links

This months top contributors

atcodedog05 19458

Qiu 15866

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703