Do you use Alteryx in a language other than English? If so, we want to hear from you! Please help us improve the international experience of our products by participating in this 5 minute survey.

We are updating the requirements for Community registration. As of 7/21/21 all users will be required to register a phone number with their My Alteryx accounts. If you have already registered, you will be prompted on your next login to add your phone number.

Engine Works

Under the hood of Alteryx: tips, tricks and how-tos.
BenMoss
17 - Castor
17 - Castor

The workflow developed in this blog post can be found here. The workflow uses the Optimization tool, which forms part of the prescriptive tool kit, so you must also have these tools installed.

 

Setting the scene

Last Christmas, I and a few colleagues looked to spread some festive cheer within The Information Lab by hosting a pub quiz. Not just your typical pub quiz though, no, that would be boring for us hosts, instead we spent countless hours building out a solution that used the technology platforms that exist within our business.

 

In the end, we created a solution that automatically posts a question, each hour, to our messaging platform (Convo), via Alteryx and Zapier.

 

Users could then submit their answers to the question via an Alteryx Application, and then a secondary ‘bot’ would post out the results of each round to our messaging platform.

 

Santa Bot.png

 

This was a fun exercise - massively over-engineered, but fun - and this year we wanted to do something similar, so we went with Bingo.

 

Clarification: what is bingo?

It seems that Bingo is a very different game depending on where you live in our wonderful, Covid-ridden world. Bingo in the UK (the game we would be playing) is a game where each participant receives a ticket (or more often than not, 6 tickets) with 15 numbers, each unique, between 1 and 90.

 

When tickets are sold in strips of 6, each strip will have each number on exactly once, meaning that every time the ‘caller’ says a number, the audience will always have something to cross off.

 

bingo ticket.png

 

The Bingo caller (the person who referees/runs the game), has a random number generator and calls out the ‘balls’ one by one until someone claims one of the many prizes available (with the final prize being for a full house which is all of the numbers on a ticket).

 

So that’s the type of Bingo we are referring to here, none of this American 5x5 grid nonsense.

 

The problem

In the UK it’s very easy to get hold of Bingo tickets, either by walking to a stationery store or by web-searching UK Bingo Tickets. However, similar to last year's Christmas quiz, we wanted to use our technologies in our quest for festive fun.

 

In this case, it turns out that generating Bingo tickets is an optimization problem, one that is difficult to solve without an advanced analytics platform (cough Alteryx cough) at your disposal.

 

Below I’ve outlined the logical rules around a strip of bingo tickets.

 

  • 6 tickets creates a ‘strip’ of tickets
  • Each strip of 6 tickets must contain each number from 1-90 only once
  • Each row must have a exactly 5 numbers
  • Each column must contain exactly 10 numbers
  • Each column within a ticket must contain at least 1 number
  • Those 10 numbers must be from a specific set, so column 1 must only contain numbers 1-10, column 2 11-20…, column 9 81-90
  • Each strip of tickets should be randomly generated

Where do we start?

We’re going to start by generating a list of the different spaces that are contained on a strip of 6 Bingo tickets.

 

Each ticket contains 9 columns and 3 rows, therefore we need to generate a list of 162 objects. In Alteryx, generating data from scratch is easy using the Generate Rows tool. In this case, I’ve used three, the first tool generates 6 records that indicate the ticket. The second tool generates 9 records which indicate the column, and finally, the third tool generates 3 records indicating the row.

 

Picture1.png

 

By creating these, one after another, we have the same effect as mashing all these different items together.

 

Picture2.png

 

The final step of this initial process is to create a unique identifier for each square in our grid.

 

Picture3.png

 

Now that we have a list of all squares in our grid, the next step is to join in the valid ball options for each square. The only constraint at this point that we can consider is that balls may only appear in specific columns, 1-10 in column 1, 11-20 in column 2, … , 81-90 in column 9.

 

Therefore after generating our list of balls (using the generate rows tool again), we can implement a simple formula which will return the relevant column number for that ball.

 

CEIL([Ball]/10)

 

This function takes the ball number, divides the value by 10, before the CEIL function then takes the result and rounds it to the next smallest whole number, greater than the value.

 

The reason we choose 10 is because that's how many numbers are allowed in each column.

 

Picture4.png

 

We can simply now join this list with our grid list to generate a data source which contains, for each square, a list of valid balls.

 

Picture5.png

 

Given that each square is linked to a column which can contain any one of 10 balls, we’re now up to 1,620 records.

 

Some additional prep

In our data we have labelled a square as belonging to a column value between 1 and 9 and a row value between 1 and 3, as shown in the grid below (sample of two tickets).

 

grid1.png

 

However, our row-based and column-based constraints are at a ticket level, and as a result we must generate unique identifiers for these. A Formula tool allows me to quickly create unique identifier for my different columns and rows.

 

Row = [Row]+(([Ticket]-1)*3)

Column = [Column]+(([Ticket]-1)*9) 

 

 

2021-01-15_14-02-25.png 

The final step of preparation is to adjust the unique identifiers that we have created. At present they are all integers, but our Optimization tool needs to be able to decipher between ball value 9 and column 9. We can use the Multi-Field Formula tool to add the field name as a prefix to the value.

 

Picture6.png

 

List of decision variables

When creating our optimization model, one of the required inputs in this scenario is a list of decision variables. In this case, we have 1,620 decision variables, each of which can either be turned on or off, and only be used once (if used at all); therefore the type field (which is required for our model) can be set as binary, or B.

 

One further element that is required by our optimization model is the coefficient. The purpose of our model will be to minimize or maximise our coefficient. In this specific example we can use the RAND() function in Alteryx to create a coefficient, which means that each time our model creates a ticket, our output will be different as each “variable” is assigned a different weighting.

 

Constraints

The purpose of this input is to inform our model of the constraints which should be in place when building our solution. In our table I must first define the constraint, then the direction (equals ==, less than or equal <=, greater than or equal >=), and finally the right hand side of the equation, i.e. what the comparative value is.

 

  • Each ball must appear exactly once

    2021-01-15_14-09-21.png

  • Each ticket must have exactly 15 values

    2021-01-15_14-11-06.png
  • Each Square must have at most 1 value

    2021-01-15_14-12-56.png

  • Each Row must have exactly 5 values

    2021-01-15_14-14-26.png
  • Each Column must have between 1 and 2 values

    2021-01-15_14-16-40.png

At this point you may be wondering what ColumnMin and ColumnMax are. These are simply copies of our Column field. When building this workflow I identified that you cannot have two constraints against the same value, and therefore by duplicating the field we give ourselves the ability to do this.

 

Our "dense matrix"

The purpose of the dense matrix is to inform our model of the contribution of each decision variable towards our constraints.

 

The dense matrix can be provided in a couple of forms but I have chosen to use the mode ‘dense matrix, variables in rows.’ The dataset looks something like this:

2021-01-15_14-22-46.png

 

As an example, if you chose to select the decision variable SquareBall1 and SquareBall2 then the tally for constraint Ball1 would be 2, but this would violate our constraints which state each ball must appear exactly once.

 

Interpreting and formatting the output

The S anchor coming out of our Optimization tool provides us with a list of variables selected by our model; this information can then be used to return the chosen position of the different balls in our strip of tickets.

 

Picture7.png

 

The penultimate step in our fantastically over-engineered process was to use the reporting tools within Alteryx to create a visually pleasing output, which we did simply by using the Table tool.

 

Picture8.png

 

The final step was to publish this as an analytic application on our server so that people could get their bingo tickets in preparation for our holiday game!

 

Now, whilst this clearly isn’t a business use case, I hope this provides another example of how we can use fun data challenges to improve our knowledge of the product, which we can then apply in the real world.

 

Comments
3danim8
5 - Atom

Hi Ben,

 

What an impressive example! I've got a lot of learning to do on this one. 

 

I downloaded the workflow and an error was triggered because ColumnMax was initialized as the default of V_Wstring instead of an Int64. Once that change was made (see below), the workflow ran fine.

 

Thanks for sharing your infinite Alteryx knowledge!

 

Ken

 

 

3danim8_0-1620841910894.png