206|ALTERYX – Hidden Messages

BYU Student Author: @Kyle_Nilsen
Reviewers: @Millie_K_B, @Jacob_Dutton
Estimated Time to Solve: 15 Minutes

We provide the solution to this challenge using:

  • Alteryx

Need a program? Click here.

Overview
You have just started your first post-graduation job as an associate at an accomplished local firm. On your first day, your supervisor approaches you with a challenge to assess your technical skills and critical thinking capabilities. He tells you this challenge will involve decrypting a message hidden within a set of text data that must be done using RegEx functions and tools within Alteryx. As you continue through the steps of the challenge, your supervisor gives you hints as to what patterns are important or irrelevant to finding the hidden message.

Instructions

  1. Download the provided Alteryx file and open the workflow.
  2. Analyze the data yourself to see if you can find any consistencies across all rows.
  3. Begin manipulating the rows using Alteryx’s provided RegEx tool:
    a. First, your supervisor points out that all spaces already in the text are erratic and serve no purpose. Remove all of them to create equally consistent rows.
    b. There will never be any three lowercase letters in a row. Remove this pattern from the string and replace it with a space.
    c. The next irrelevant sequence is an uppercase letter, a symbol, and another uppercase letter. Replace any instance of this sequence with a space as well.
    d. Remove any instances of the sequence letter, number, symbol, and replace them with a space.
    e. Finally, you learn most characters are actually completely irrelevant. The only relevant character is one that immediately follows a symbol and a letter sequence. Find these characters and separate them into their own columns using the Tokenize output method.
    f. Use a summarize tool to join a whole column’s rows into a single cell. Do this for both of the character columns you created in the last step.
  4. After your manipulations, you notice an interesting message arising from the remaining cells. It seems to be a cipher of some sort. Use your critical thinking to decode your final message from the hint in column 1.

Data Files

Suggestions and Hints
  1. Alteryx is known for being very user friendly in that it walks you through the basic functionality of each tool. You can do this by clicking on the tool and then selecting “Open Example,” which will open a new workflow that goes through the examples of many different aspects of how the tool can be used.
  2. If you have never used RegEx, don’t fret. The Alteryx UI has a drop-down menu on the configuration pane section called “Format to Convert,” next to the text box for “Regular Expression,” indicated by the + sign. Click on it to see the many different special characters you can use to format your regular expression and use what applies. Alternatively, check out https://regexone.com/lesson/introduction_abcs for an introductory lesson to regular expressions.
  3. Hidden here are the specific expressions I used for each step:
    a. \s
    b. \l{3}
    c. \u\W\u
    d. \l\d\W
    e. \W\d(.)
  4. Use the concatenate function found under the string dropdown to group the column into one row. Make sure the “Concatenate Properties” separator is empty (default is a comma).
  5. The hint points to a simple Caesarian cipher. Simply go back one letter (or keyboard symbol for the last character) in the alphabet to decode the message in column 2.

Solution