Dynamic Replace

Need help in configuring the Dynamic Replace tool.

I have a two column excel file: From & To. For example

from to

1 Y

0 N

I have used the dynamic replace tool to replace values in a column per the above table. But, if my input column has 'Y' it becomes NULL because it is not part of my table above. I don't want to have put every possible values in the column in the above table as the input data may vary and that is okay with me.

I just want what is in the file to be replaced and leave every other value as-it-is. I can't seem to get Dynamic Replace to do that.

Is there a hack to do this or a combination of some other tools perhaps?

Developer Tools

Tips and Tricks

Transformation

Accepted answers

patrick_digan

@dostabhi What about something like the attached where you add 1 more record to catch everything else and leave it untouched. I'm guessing your current setup is similar to the left side of the workflow (on a much bigger scale). That shows that unmatched values end up as [null]. On the right, I've made a few tweaks to fix this:

1) Changed the Output Value to be quoted since I'm going to call it a formula now.

2) summarize my fields and add a record with boolean = 1 and output value = [_CurrentField_].

3) Union original rules with step 2 above and forcing the output order so that step 2 comes last. Since this is a catch all, we need this one to run last.

4) change the dynamic replace so that the box is checked for values are expressions/formulas.

Community 1-29-18.yxmd

All comments

patrick_digan

@dostabhi Would the find replace tool accomplish what you're after? That would certainly fix your null issue, but perhaps there is another reason why you're using the dynamic replace tool.

dostabhi

Thank you for your response, I'm using the Dynamic replace because my reference file is actually in following format:

Field From To

A 1 Y

A 0 N

B '' Unknown

B x y

Now field B can have multiple values which i would want in my output to be as-it-is. I'm converting the From to a Boolean expression using a formula tool before feeding it into Dynamic Replace Tool. I followed this post: https://www.theinformationlab.co.uk/2015/03/05/alteryx-tools-focus-dynamic-replace-dynamic-rename/

patrick_digan

1) Changed the Output Value to be quoted since I'm going to call it a formula now.

2) summarize my fields and add a record with boolean = 1 and output value = [_CurrentField_].

3) Union original rules with step 2 above and forcing the output order so that step 2 comes last. Since this is a catch all, we need this one to run last.

4) change the dynamic replace so that the box is checked for values are expressions/formulas.

Community 1-29-18.yxmd

dostabhi

Thanks a ton! Worked like a charm.

Quick Links

This months top contributors

mceleavey 383

mbarone 337

Hollingsworth 335

LanisC 335

JeffF 335