2020: Week 20 - Solution

You can find our full solution workflow below and download it from the Preppin' Data Community page!

This week we are looking at solving an encrypted message by using a couple of techniques that aren't native to Tableau Prep. These techniques include splitting a string that doesn't contain any delimiters and also concatenating a string when aggregating.

Step 1 - Input Encrypted Message 

The first step is to input the Encrypted Message data table, this is one row that contains our encrypted message. 

To help us split the string into the correct number of characters, we first need to identify how many characters there are in total. We can use the LEN() functionality to determine this: 

Length

len([Encrypted Message])

We now know that there are a total of 23 characters in our message.

Step 2 - Join Scaffold

The next step is to input our scaffold data set, then we can join this using the newly calculated Length field. Our join conditions look like this:

This creates a new row for each number less than or equal to the length of the message. In our case it creates 23 rows in total:

Step 3 - Split String

The next task is to split our string so that we have a character on each of the rows. This is where the scaffold comes into play as we can use this along with the MID() function to extract each of the characters as we go along. We use this calculation:

Encrypted Value

mid([Encrypted Message],[Scaffold],1)

We now have each of our different characters on an individual row:

Step 4 - Join Cipher

Now each character has it's own line, we can join the cipher data table so we can identify what each of the encrypted characters can be translated to. We use the following join conditions for this:

Notice that we have used a left join so that we return all of the rows from the Encrypted Value table. This is due to some of the characters being repeated, therefore we want to make sure all of them are returned and not just the first time it occurs.

Step 5 - Pivot Values to Columns

The next step is to remove any fields that we no long require, these include:

• Length
• Encrypted Value
• Cipher
• Encrypted Message

We also want to replace Null values with ' ' (space).

Once we have cleaned our table, we can then use a Rows to Columns pivot so that each of our characters are within it's own column. The pivot setup looks like this:

Step 6 - Concatenate String

Now we have all of the characters in their own columns, with the scaffold as the heading, we can use a standard string calculation to add all of these together. This is a manual method that could take a while if doing this with a long string, but luckily ours isn't too bad! The calculation looks like this:

Message

[1]+[2]+[3]+[4]+[5]+[6]+[7]+[8]+[9]+[10]+[11]+[12]+[13]+[14]+[15]+[16]+[17]+[18]+[19]+[20]+[21]+[22]+[23]

We can then Keep Only on the Message column so that we are left with our output that looks like this:

Make sure to fill in the participation tracker, share using #PreppinData on Twitter and post you solutions onto our Tableau Forums community page so that we can compare our workflows!