Challenge #35: Data Cleansing Practice

[Nicole_L]

Tricky formulas but learned a new one today.

[beccat]

Great practice and prep.

[p-g]

I have tried multiple solutions for each of the problems.

Spoiler

I have used Formula Tool to arrive at the solution. I have also alternatively used Regex - Replace, Regex - Tokenize and Regex - Parse to arrive at the solution. This was a fun learning experience and introduction to various Regex options available.

[Sashikumar]

Easy

Spoiler

[vikb123]

Here's my solution!

Spoiler

Solution

[A2H]

Spoiler

[moray]

Hey Tony,

I'm relatively new to Alteryx and am working my way through these. I wonder if you might help me on an alternative solution.

I missed your super-simple: "SC"+[Safety Code], probably because I over-thought it.

I had originally tried, unsuccessfully, to use PadLeft, using variants of:

IF Length([Safety Code]) == 6
THEN PadLeft([Safety Code], 1, 'C')
ELSE [Safety Code]
ENDIF

Then repeated for "S"

Any idea why using PadLeft didn't work?

Cheers,

MB

[greggrimmer1]

Spoiler

[p-g]

My updated solution with additional different methods of solving the challenge.

Spoiler

I have now used various Regex Tool Modes (Replace, Parse, Tokenize) as well as Formulas (RegexReplace and ToNumber) to arrive at the solution. Great workout for the brain.

[p-g]

Hi MB,

Pad Left and Pad Right functions are used for adding fillers rather than prefix strings. For example, if you have 1234, 123 and 12 under Field1 where field length has been specified to be 4 characters long. You can use PadLeft([Field1], 4, "0"). This would result in the output 1234, 0123 and 0012. Similarly PadRight would result in 1234, 1230, 1200. Hope this helps! Once you get comfortable with Regex Tools and RegexReplace Function, you can use it prefix any string too. I have just posted my solutions using multiple modes of Regex and different Formula Tools. Hope this helps!