Challenge #496: Create the Correct Code

[AYXAcademy]

Hi Community members,

A solution to last week’s challenge can be found here.

This challenge was submitted by John Primeaux (@jeprime) . Thank you, John, for your submission!

This week, you’re stepping into the role of a data quality detective. A data table has been populated with critical tracking codes—but many of them have been entered incorrectly.

Each code is supposed to follow a very specific structure. This format is vital because these codes are used to locate corresponding documentation within a larger database. Your mission: clean them up and restore order.

The required format is 1 to 4 alpha characters, a hyphen, 3 digits, a hyphen, 2 digits, then one optional alpha character.

The numeric portions (3-digit and 2-digit groups) are always generated correctly by the system. However, delimiters (- vs. _) may be missing or incorrect.

Hint: Assume that the three digits and two digits inside the code are created by a computer; therefore, the hyphen is never missing.

Task 1: Create the code in the correct format (using only hyphens "-"), including the optional letter suffix, and compare it to the old code.

Task 2: Calculate the percentage of codes with a letter suffix, as well as the percentage of codes that are missing or contain incorrect delimiters.

Once you have completed your challenge, include your solution file and a screenshot of your workflow as attachments to your comment.

Good luck!

The Academy Team

Download Start File

Source: Dataset generated by challenge creator.

[Bobbyt23]

Didn't quite get the same results as the outputs on the challenge but my result looks correct.🤔

Spoiler

[jrlindem]

Another fun challenge.

Narrative explanation:

Spoiler

I broke mine up into separate regex components and then stitched them back together for the first Task.  I did note that the third record, in the provided solution did not adhere to the challenge rules so my answer differed on that record.  See Red Box (my solution) vs Yellow Highlight (authors solutions)

For the second task, I also had a result deviation.  I checked it manually and believe I have the right percentage but welcome to others feedback if I missed something.

Here's my workflow result:

Spoiler

[TurboToad]

Ended up with some slight differences than the solution. 

Spoiler

[jmgross72]

Unsure about the Output Solutions.  It's entirely possible I missed a step or am not understanding correctly, but I did my best! Let me know if you find my mistake.  

Spoiler

Other than row 47 in this screenshot, I completed task 1. I believe this is an error in the start file.

I also got different percentages for Task 2.  After physically counting the outputs from Task 1 with suffixes, I believe my output is correct. 

[lwolfie]

Took me a minute to figure out the ask for Task 2 and what constituted an error compared to the answer for Task 1.  I am excited that I was able to get it to work in a formula tool and didn't need multiple RegEx, though. :)  Progress!

[christouf03]

Thank you for the challenge. I tried many times to see if something was wrong but despite the fact that I could not match exactly the results provided, I have confidence in my results

Spoiler

[laverdewill]

Spoiler

I was able to match the output of task 1, but attached you can find my version of how I think it should actually be. For Task 2, my percentages differ from the output.

[Pilsner]

Great challenge to freshen up my regex skills!

Spoiler

I, too, initially got a different answer for both parts; however, the hint was helpful in getting part one to match. I also didn't like the idea of making the regex any more complex than it already was, so I removed any other delimiters before reformatting the code.😄

[Kilbyflow_Designer]

Complete!!

Spoiler