Challenge #2: Preparing Delimited Data

[CASaiRam]

Solution

[gawa]

Regex is great.

Spoiler

[mlellison]

Spoiler

The challenge is how to split string value when it contains multiple delimiters
e.g. "Mary had a little lamb whose fleece was white as snow",123,'16-JUN-01'

1.) Text to column: Split by comma
2.) Regex: replace " with nothing, replace ' with nothing
3.) DateTime: Convert date (string type) to date (date type)

Alteryx solution used 1 Formula function to replace my 3 functions (2 regex and 1 datetime)
Alteryx approach as below

1.) Text to column: Split by comma
2.) Formula: Trim([1], ' " ') & DateTimeParse((Trim([3], " ' ")),"%d-%b-%y")
     [1] & [3] are variables, which means the string value of column named "1" and the other column named "3"
     %d-%b-%y, not sure about this part, guessing b represent month, it then convert the original string value datatime to a default datetime yyyy-mm-dd

Much better, as less function used, but achieve same goal

[jyap13]

My solution as below.

[gawa]

As cannot edit last post, upload solution as a new post

[floresls]

Fun exercise. Nice to be able to practice text to columns.

[bnichiosain]

Solution 

[olivier_newman]

q

[phdataviz]

Weekly Challenge: Preparing Delimited Data

[perin]

Good way to start in alteryx :)