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?

Help

Accepted answers

DataNath

@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>,.....)

Switch for multiple replacements.yxmd

All comments

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

190123help4.yxmd

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.

Money

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

Which makes the whole column -

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.

DataNath

@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:

Switch for multiple replacements.yxmd

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

DataNath

Yep exactly @BS_THE_ANALYST - I also think for a larger list of targets then lookup tables are cleaner/easier to maintain, especially if handing over workflows etc. But for cases where there's 2-3 changes needed a Switch() is sometimes just a nice alternative!

Quick Links

This months top contributors

atcodedog05 19458

Qiu 15866

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703