Introduction

The Alteryx technical communication is very active.  In addition to the learning materials available in Alteryx Academy, they also provide various business scenarios through the “Weekly Challenge”.

Anyone can solve the challenges posted in the community, this helps members to apply their learning, collaborate and discover several different approaches to the problem.

In this blog, I will discuss in detail one such solution to a challenge that was posted on week 168

Challenge Description

Consider you have three ordinary dice. You should roll all three dice and compute scores in the following manner.

• Multiplying the largest number with the second largest number
• Add the remaining value
• By this method of scoring find the most occurrence of the score
  

GIF Source: https://community.alteryx.com/t5/Weekly-Challenge/Challenge-168-Dice-Game-Born-to-Solve/td-p/427239

To make you understand easier, I have taken two probabilities of the three dice and illustrated the steps of the workflow below.

Step 1: Get the Dice input

Let’s have one text input tool each for 3 dice with the value of 1 to 6″

Step 2: Calculate the Probability

Generally, the append tool is used to perform the Cartesian Join. Here cartesian join will append the value of one input to each record of another input.

By this, I have taken the two-text input to generate the probability of two dice. Then the output of this Cartesian is taken as input for the next append tool to generate the probability of three dice which is 216. (6*6*6)

Step 3: To Calculate the largest and Smallest Number

Now, the largest and smallest numbers can be calculated by –

• Pivoting the columns
• Sorting the output
• Adding record id to find the largest and smallest value

We will look into the details of these steps below.

3.1  Pivot the Column

Pivoting of columns can be achieved by the Transpose tool. It rotates horizontal data into the vertical axis. Before rotating the column value, we should have some unique ID to track the column values after doing transpose. Hence, I have used the Record ID tool to create a unique ID for each column and then performed the transpose.

3.2  Sort the Output

Now the data is arranged in a vertically. In this state, Sorting must be performed to order inputs from smallest to largest based on two columns Record ID and Value.

3.3  Add Record ID to find the Largest, Smallest value

Again, I’ve added another record id as the first column to determine records having the largest, second-largest, smallest in the following manner.

Output = Perform modulo operation on Record ID by 3

Step 4: Find the occurrence of Score

Then we will find the number of occurrences of each score by unpivoting the column and implementing the scoring method. We will look into the details of each step as below.

4.1  Unpivot the Column

The crosstab tool rotates vertical data into the horizontal axis. This tool needs two inputs, column 1 as a header, column 2 as value.

4.2  Implement the scoring method

Once the data is arranged horizontally, we can perform the calculation using the formula tool. Then leverage the summarize tool to find the occurrence of each score. In this case, I’ve used the sample tool to fetch the topmost record.

As displayed above, there are 216 probabilities when three dice are rolled and the maximum occurrence of a score out of these 216 probabilities is 13.

The final workflow will look like below.

In subsequent blogs, we will demonstrate similar features and usages of Alteryx to resolve other weekly challenges. I hope you can leverage some of these options and capabilities to address your enterprise data blending and data wrangling needs.

Read more about similar Self Service BI topic here and learn more about Visual BI Solutions Microsoft Power BI offerings here. 

https://community.alteryx.com/t5/Weekly-Challenge/bd-p/weeklychallenge