2022 Week 41 - Solution

Solution Video

Solution by Tom Prowse and you can download the workflow here.

This week's challenge is a continuation of last week, where we created a multiplication table using a static input. This week we want to take that a step further and make it dynamic based on a user input.

If you haven't already completed 2022 Week 40 then I would recommend doing that one first as we are going to use that workflow to start this solution.

Step 1 - Create Parameter

First we need to create the parameter that will allow the user to input a number to create the table. 

This is a simple parameter that uses a Whole Number and is required to have an entry at run time: 

By creating this parameter we can now input any number and our table will create a row and column up to that number.

Step 2 - Filter Table

Next we need to use the parameter within our workflow so that it creates the table based on the correct numbers. 

We want to go to the start of the workflow where we have our current input rows of 1 & 9. On the first clean step, we can use a calculated field to replace the 9 value with our select number in the parameter. By replacing the 9, this will allow our New Rows step to make sure we have the rows between 1 and the chosen number. 

We can use the following calculation to replace the 9: 

Numbers 

IF [Numbers] = 9 THEN [Parameters.Select Number]
ELSE [Numbers] END

At this stage we should still have 2 rows... 1 and then our chosen number.

Step 3 - One Row for Each

As we are allowing the user to add any number, we need to make sure we don't have repeating rows if the user inputs 1. As a result, at the moment this will create two rows, both with a 1 in them and therefore cause some duplication later in the workflow.

To overcome this, we can add an Aggregation step, between the first clean and the New Rows step, and make sure the Numbers field is in the Group By. This will then return only 1 row for each given number.

After this our workflow should work as expected and we should be able to create the dynamic table. 

Please Note: Remember last week we encountered a slight bug within Tableau Prep so you may need to run the first part of the workflow, and then update the input for the second part.

If 4 is selected then our output should look like this: 

After you finish the challenge make sure to fill in the participation tracker, then share your solution on Twitter using #PreppinData and tagging @Datajedininja, @JennyMartinDS14 & @TomProwse1

You can also post your solution on the Tableau Forum where we have a Preppin' Data community page. Post your solutions and ask questions if you need any help!