For my first blog, I thought it would be interesting to describe how my mind worked at the very start of my Alteryx journey, in addition to providing mine and my fellow DS18 member Angelos’ workflow. This being (basically) my first Alteryx work flow, please excuse for any badly-designed work flow/poor tool choice! I welcome any feedback, especially on steps people might regard as not best practices (for example the date creation step).

Please see the Preppin’ Data site for the full information on the challenge: https://preppindata.blogspot.com/2019/03/2019-week-6.html.

Essentially, the challenge requires you to use data provided in two separate tables and aggregate them together to produce a profit value for March. This then needs to be joined to another table which contains the values for other months.

Here is my overall workflow for the challenge:

Step 1: Input the England Mar-2019 table and split the columns.

We started by inputting the England Mar- 2019 table (sheet one of the excel file). We started with this table for no reason other than it seemed the logical place to start. The first step we did was to parse the category column into two columns and rename the column we wanted, so it matched the Soap Pricing table that we input later. We used the Text to columns tool to parse the text and then the select tool to rename the column and drop the unwanted columns. See below:

Step 2: Summarize the data and add a date field for the England – Mar 2019 table.

The next step we took was to use the summarize tool to aggregate down the Mar 2019 table into the sum of units sold for each type of soap:

We then added in a date field, using a formula tool. We decided to do this because we could see that the final table we join the results to contained a date field (the Company Data table). I am fairly sure this is not best practice – we literally typed in the date as as string as we knew the value and there were only two rows. So, if you’ve made it this far into this blog (unlikely) please let me know the correct way of doing this!

Step 3: Join the data with the ‘Soap Pricing’ data

The next step was to use the join to tool to combine our current table with the soap pricing data. The tool was joined on the ‘Type of Soap’ column and the unnecessary duplicate columns dropped. See below:

Step 4: Calculate the profit

Now our tables were joined, the next step required us to calculate the profit. This was done with the basic formula below:

The unecessary columns ‘Units sold’, ‘Manufacturing cost per unit’ and ‘Selling price per unit’ were then dropped using the select tool.

Step 5: Union current table with the final ‘Company Data’ table

The final step in the process was to join our table with the final output table using a union. We manually configured the join as below:

The final output table came out as so:

So that was the first challenge we completed without the complete guidance of a coach! I hope this blog will be useful for people who are curious about the solution, but also as a way to demonstrate how we, as beginners, decided to tackle the challenge.

I am sure that as I progress through the Data School, I will look back upon many of my early challenge attempts and see the error in my ways – but for now I was just happy to get to the correct solution! If you have any feedback on how the workflow could be improved, please don’t hesitate to comment.