Challenge #83: Pop-Tart and Beer Pairings

The solution to last week's challenge can be found here!

This week's challenge was submitted by the illustrious @MarqueeCrew who came across this challenge at a Tableau User Group! Yes - the problem really was around beer and Pop-Tart pairings.

At a recent Tableau User Group, a poll was conducted using SurveyMonkey and the data was exported to an Excel workbook. Unfortunately, the data wasn't in a format that made it easy to work with in Tableau. Fortunately, an Alteryx user was there to prep the data on their behalf.

Within the survey data, there are repeating groups of Pop-Tart, Beer and Score values. Yes, the TUG had samples of Pop-Tarts paired with the beers from Bell's Brewery. The task was to create a single column for each of these values, allowing for repeating the balance of the survey results.

Can you Normalize the output data? For bonus play, can you analyze the data and provide insights from the sample data?

The output should include:
1. Name of the fiber
2. The original spatial line object
3. The new spatial line object
4. New length of line

This is my vote for best pairing.

challenge_83_start_file.yxmd

challenge_83_solution.yxmd

Parse

Join

Preparation

Advanced

Data Analysis

Intermediate

Data Preparation

Accepted answers

All comments

nick_ceneviva

Can't say I have ever paired beer and poptarts together before! Solution is attached.

Spoiler

I handle the pairing with the dedicated columns through transpose and then splitting the column name using the Regex tool. For the custom pairings I used more of a brute force approach where I just renamed the fields and unioned everything together. There has to be a better approach though.

I then filtered out the null poptart rows and union the custom and standard fields together.

gnans19

Tried without RegEx

Spoiler

challenge_83_Gnans.yxmd

Joe_Mako

Spoiler

Four reasonable tools (the two Multi-Field Formula tools could be replaced with a single Formula tool, but it would be a pain to write all those expressions):

- Two Multi Field Formula tools to add the additional two columns for the main combos
- Arrange to reshape the data
- Filter to remove null records (removing records with null Pop-Tart matches the expected output)

I am not sure if there are any conclusions that can be drawn from the data, but here is a Linkert chart of the main combos:
Beer - Pop-Tart Linkert.png

challenge_83_Joe_Mako.yxmd

JoeM

I knew that the @Joe_Mako Arrange tool signature move would show up here! Love seeing you break that out every time!

BenMoss

I've tried to make this as dynamic as possible in case additional fields come into the data, etc.

Spoiler

@Joe_Mako's solution looks great, need to take a look at this in more detail!

challenge_83_start_file - BM.yxmd

pjdit

I handled the pairings with the dedicated columns through transpose and then splitting the column name using a simple replace formula, then split them out with the text to columns. then for the special pairings I used the arrange tool to create the 3 fields. then union the data together.

seems to work pretty well.

Paul

Natasha

I've never used Arrange tool before, so thanks @Joe_Mako for showing the use case for it.

My solution attached, but I could probably improve it and get rid of the Join tool.

Spoiler

challenge_83_NK.yxmd

patrick_digan

Spoiler

I used transpose and cross tabs like most other people. It's not as dynamic as some of the others here.

challenge_83_start_file.yxmd

David-Carnes

Went down a rabbit hole by using the Field Info tool, selecting column with a Formula, then trying to dynamically pull data from the desired columns. But when I tried the Dynamic Select tool things fell into place pretty quickly. I had never used the Dynamic Select tool before, so this has been a fruitful exercise.

Spoiler

083 challenge.PNG

This can handle the additions or deletions of columns.

Edwin_dup_98

Here is my solution.

challenge_83_start_file solution.yxmd

JoeM

The solution is now posted!

vishalgupta

challenge_83_Vishal.yxmd

BenMoss

Can't say I've ever had a pop tart (I don't particularly enjoy beer either!) but here's my solution.

Spoiler

challenge_83_start_file - BM.yxmd

estherb47

Another really fun one! I wanted to make a solution that's as dynamic as possible, so it's easy to add in new beer and pop tart pairings

Spoiler

Separated the columns into 3 types, using dynamic selects
-stream 1 is for the key fields
-stream 2 is for the columns that were titled with the pairings. A transpose into a Regex parse separated the beer and pop tart name
-stream 3 is for the columns that listed beer, pop-tart, and score one after the other. Similar approach to stream 2 with a transpose, then cleaned up the "name" column to just have pop-tart/beer/score. Needed a new key field for each unique set within the transposed data (multi-row formula took care of that), and then cross-tabbed the data set back into proper columns.

a RecordID tool before splitting the streams made it easy to rejoin.

challenge_83_EHB_solution.yxmd

NicoleJohnson

My solution! Should be dynamic for any number of beer & pop-tart combinations. Slight difference in results from posted solution because I removed any records that didn't have a score (some of the records had a pop-tart identified, for example, but no beer or score. I figured those were superfluous to any analysis that would be done on the results).