Formula tool making the whole column 0

[ShantanuDagar]

I am using formula tool to replace certain words with another in an existing column.

So using replace function.

And there are multiple words which need replacement so using AND in between multiple replace().

But after using that, all values in the column turn to 0.

Standalone 1 expression for 1 word replacement is giving right result but clubbing together multiple gives all 0

What is the issue?

[BS_THE_ANALYST]

Try 'Layering/nesting' the replace functions. So, replace(replace(string,targ,replace),targ,replace) ...

essentially its starts on the inner replace, i.e. replacing the first thing you wanted to replace in the string. It then takes the result of that, and replaces the 'new' string with the next replacement

[BS_THE_ANALYST]

See this: first it replaces the question marks, and then the fullstops: Replace(Replace([test], "?", ""), ".",""). Attached the workflow aswell

[ShantanuDagar]

@BS_THE_ANALYST 

In 1 cell, only 1 part need replacement.

Consider example: In 1 excel sheet there is acronyms or something like that and changing them to little full form to match other existing sheet.

As                            As to be replaced by Asset. Lb to be replaced by Liabilities. And FD to be replaced by Fixed deposit.

Lb

Lb

Lb

Money

FD

So using - Replace("As","As","Asset") AND Replace("Lb","Lb","Liabilities") AND Replace("FD","FD","Fixed Deposit").

Which makes the whole column -

0

0

0

0

0

0

[DataNath]

@ShantanuDagar by any chance, when you're using the AND operators, are you typing in something like [FieldName] = Replace([FieldName],'target','replacement')? If so, the reason you're getting 0s is because - by typing '[FieldName] =' - you're actually telling Alteryx to check whether the field = the field after replacements, which it obviously doesn't. In boolean values, false = 0. To get around this then simply nesting the functions like @BS_THE_ANALYST suggested ought to work! Or you could build a reference table and use a Find Replace table.

[BS_THE_ANALYST]

@DataNath approach is what you want to use here with the Find and Replace tool I believe. 

Before pic:

After pic:

You just need to create a lookup table for this to be able to happen. Find attached the workflow! I'd recommend clicking on the Find and Replace tool in Alteryx and then looking at some of the examples it provides. They helped me alot a couple of weeks ago :).

[DataNath · Accepted solution]

@ShantanuDagar sorry was typing while you posted so didn't see the example you provided. For something like that I'd probably recommend using a switch function like so:

What's going on here is you're saying: Switch(<Field you're looking in>, <Default value i.e. if you don't find any of the target/replacement pairs we're about to provide, populate with this>,<Case 1 - if you find this>, <Replacement 1 - replace with this>, <Case 2 - if you find this>, <Replacement 2- replace with this>,.....)

[dYoast]

@ShantanuDagar 

By putting the AND in the formula, you are making it a boolean function.  This is why it gives the result of 0

If you nest the function, it will work.  You will likely have to change the length of the column to accommodate the new text.

For my test, I used this formula:

Replace(Replace(Replace([Text], 'FD', 'Fixed Deposit'), 'Lb', 'Liabilities'), 'AS', 'Asset')

[BS_THE_ANALYST]

First time I've seen the switch function! Looks really good @DataNath. So for entire field matches, we could opt for the switch function, but we'd opt for the Find and Replace if we want more flexibility?

[ShantanuDagar]

Thanks