2021-W31-Alteryx.md

Preppin Data 2021 Week 31 - Alteryx Solution

Solution by Luis Garcia Fuentes

For more details on this weekly challenge visit Preppin Data

Excelling in Prep Challenge

Link to Challenge - https://preppindata.blogspot.com/2021/08/2021-week-36-excelling-in-prep.html

Details

The weekly sales of Bike Components from Preppin's bike store Allchains is what we are analysing. The returns are where the product has been deemed faulty before it's sold.

Requirements

• Input data
• Remove the 'Return to Manufacturer' records
• Create a total for each Store of all the items sold
• Aggregate the data to Store sales by Item
• Output the data

Expected Output

My Solution

1. Input the data
2. Filtered out the items where the status did not equal "Return to manufacturer"
3. Changed the "Number of Items" field from a string to an integer for use in future steps

4. Used the Cross Tab tool to separate the "Items" column into separate columns per distinct item

• At this point you have half of the solution, only the total items sold per store are needed

5. I used the Tranpose column to turn the separate item columns back into one column containing the number of items sold

6. Used the summarize function to sum the number of items sold per store

7. Joined the Items sold per store to the output of Step 4

8. Results

Full Workflow