Part of me, the Italian part, was most pleased to see that we were analysing data about three of life’s essentials: beer, pizza and ice cream. Head coach Andy visited Rome back in April and chronicled his indulgences across the city (pictures and all!).

He had collated his data into an Excel spreadsheet, one tab for each element of the essential trio. So how were we to get those separate sheets together into one for Tableau? Well, we had Andre to the rescue!

Taking us through the basics of macros in Alteryx, Andre showed us how to create a batch macro using the Control Parameter tool. By controlling the way the input tool pulls in data, we set up a macro to collect each of the sheets separately, then union them together all in one step.

An input batch macro in Alteryx. The clapperboard is the tool used to program the Control Parameter.

Settings used in the input batch macro. By selecting the File to be updated, the macro will pull in every sheet from the excel file.

The macro can be instructed to search through the data and update the value given in the FileName field for each incoming sheet. Thing meant that rather than just importing the sheet named ‘Beer’, it would import all three. The same macro could be used to import any number of sheets – the batch will continue to run until all the sheets in the file have been imported.

Once the macro was ready, we could get to work setting up an Alteryx workflow to prep Andy’s data. The input the macro needed was a list of the sheet names in the file, which it could read as being different each time, allowing it to pull all of them into the workflow.

Settings used in input tool to get a Sheet Names field and a FileName field.

Fields generated by the input tool for use in the batch macro.

Alteryx workflow set up to import sheet names into the batch macro, followed by data processing for use in Tableau.

A few calculations, parses and transpositions later, out came a set that allowed me to look at his eating patterns quite nicely.

Interestingly, I noticed that Andy’s average rating out of five was the lowest for beer by almost half a point. While he clearly loves ice cream, the Italian beer seems to have been less impressive for him.

While looking closer at his beer choices, I noticed that he slightly preferred bottled beer to draught, and while each brand of beer seemed to be quite similar in his eyes, one stood out above the others. So if you’re every getting a round in for Andy in Italy, get him an Appiantica!