Challenge #35: Data Cleansing Practice

[Chandan79]

Here is my solution to the challenge.

Best,

Chandan 

[jyothiki]

Good for practice.

[OwenBData]

My submission!

[JasonHu]

Here is my submission:

Spoiler

[mithily]

I used regex for the second, a simple select change for the first and then the good ole formula tool for the last two. This was easy but very nice to do 

[simbert]

Never used the trim function so much in my life!

[collinalldata]

Here's my solution. Almost got them all in one tool!

[Steph_Maddrell]

Solution attached

[rajatsadan]

Hi @GeneR ,

Interesting challenge!! Please find the solution. I am having one doubt regarding the last data cleansing question. 

1. If more than 8 chars, remove anything after 8. If only 6, add “SC” to the front.

Spoiler

Spoiler

For solving this I used formula tool and inserted "IF ELSE' condition to check both conditions. 

My statement is as follows to get the solution  :

if Length([Safety Code])=6 then [Safety Code]+"SC"

else if Length([Safety Code])>8
then Substring([Safety Code],0,8)
else [Safety Code]

endif

endif

My doubt is that, why do I need to enter two 'end if' statements while closing the formula??

The standard formula in Alteryx tab is IF c THEN t ELSEIF c2 THEN t2 ELSE f ENDIF

When I tried closing the condition with a single 'end if' statement, the workflow shows the following error: Error: Formula (157): Parse Error at char(150): Malformed If Statement (Expression #1)

So I had to add the second 'end if' statement. 

Would be helpful if anyone could throw some light. Thanks!!

[TheOC]

Hi @rajatsadan ,
I've just had a look at your workflow and I understand the problem you were facing, as it's something I've done before too! You are right to say that the typical if/else statement should only have one 'endif' to end it. What I found is that you had opened a second if statement in your second line of code:

Spoiler

The correct syntax would be 'elseif', as the space opens up the else statement, and its possible to have more if statements within an else statement.

I have attached a workflow with a fixed formula, with the second endif removed and the space removed!

I hope I explained it in a way that makes sense, but if not feel free to PM/DM me and i'll try to explain it further 🙂