Dynamic Replace Tool unmatched fields unchanged instead of null

[iCFO]

I have built a pretty extensive data prep workflow that is currently dependent on the Dynamic Replace tool. Unfortunately I have just discovered that "Unmatched data fields are replaced with [Null] values" really means that once you replace a given value for a field name, you have to replace every single value for that field or it replaces them all with Nulls... (I am still struggling to comprehend the designed purpose of wiping out that data instead of leaving it unchanged!)

My workflow uses an external control module to direct the Dynamic Replace Tool to replace necessary data across hundreds of columns and thousands of rows of data. I can easily adjust the control module to work around the issue by listing out every "unchanged value" and "replacing" it back with the original value, but that is a staggering amount of replacing! I doubt that would be feasible if I needed to handle millions of rows of data.

Has anyone found a workaround for a large scale Dynamic Replace that leaves unmatched data fields unchanged? Is there a way for me to get into the workflow of the tool itself to try and adjust it for my needs?

Thanks!

[danilang]

Hi @iCFO 

Check out the attached to see how to use the Dynamic Replace tool in formula mode to have it pass non-matched values through to the output.  Maybe you'll be able to use some variant of this. 

Dan

[iCFO]

I haven't been able to pull it off with that approach thus far. From my understanding Dynamic Replace seems to be driven by a basic Boolean output of 1 / 0 (True / False). When it is 1, then it will replace as directed by the expression. When it is 0, then it is hard coded to replace with nulls. This means that any formula that I write must always return a true value to avoid the hard coded null replacement of the Dynamic Replace Tool. Essentially that brings me back to the only 2 formula approaches that I have been able to devise.

1. An IF statement that Replaces the values with themselves within the formula upon fail (which I have been unable to successfully write) Even if I did, it is still needlessly replacing what will potentially be millions of unchanged values.

2. An IF statement that moves down to the next formula row upon fail, which could potentially side skip the Boolean 0 output and move on to the next value. (which I also have been unable to successfully write) This approach would skip false rows, but it is way beyond my expression writing skill level assuming that it is even possible.

I have found a much more complex workflow that uses an iterative macro approach and seems to achieve the goal of replacing without defaulting unchanged values to a null replacement, but I haven't been able to get it to work with a more simple Vstring replacement yet.

https://community.alteryx.com/t5/Alteryx-Knowledge-Base/Tool-Mastery-Dynamic-Replace/ta-p/121655

[danilang]

Hi @iCFO 

Can you describe, in as much detail as you can, what you're trying to achieve?  We can probably work together to come up with a solution.

For your point 1.  This is the accepted way of handling this case.  The calculation engine behind Alteryx is blazingly fast.  A formula of the form 

if mod([RowCount],2) = 0 then
    [RowCount]*2
else
    [RowCount]
endif

executes across 1 million rows in 0.186 seconds.  I'm not privy to the internal workings of the engine, but there's probably an optimization that deals with this specific case of replacing the field with itself.  

Dan

[iCFO]

Here is a simple workflow that highlights the null issue. I am struggling to get a feasible IF expression to work with the Dynamic Replace Tool when the field type is set to VString. Once I check the "Values are Expressions/Formulas" box, everything I try gives me Parse Errors.

[iCFO]

Here is a workaround that I thought up. It is not very elegant, but it looks to be functional for now. I took the approach of building all of the non-replacements and putting them through via Join / Union / Formulas. The Dynamic Replace expression is an If statement, but that step isn't really necessary with this structure since I rebuilt everything for replacement anyway.

[danilang]

Hi @iCFO 

Now that I understand what you are trying to accomplish, here are 2 more ways to get there

This first one builds an expression to be executed in the formula tool and passes it to the dynamic replace, operating in formula mode

The second one  transposes the data and joins it to Rename Value Input on Field and Export Value.  The Join re-orders the fields so that the Union can config by position.  The cross-tab restores the original Data Input structure

Try each of three variants on your full data set and let me know the results.

Dan

[iCFO]

The formula mode looks like it is probably going to be the winner. I added a bit to the Dynamic Rename summary output to reflect the actual change count and added some more details for tracking and logging changes. I will run a larger database through it on Monday to give it a final test and let you know.

Thanks a ton @danilang ! I was able to glean some insights from both of your approaches. I am sure it will help others who are on a similar mission in the future as well.

[iCFO]

I made some minor adjustments after testing on a larger scale. Combining the formula mode approach to drive the Dynamic Replace and the Transpose and Join approach to rebuild the Summary area is by far my preferred workflow at this point. Leading and trailing white spaces present in the "Export Values" of the original "Data Input" were being filtered out by the time the reached the "Rename Value Input". This caused the Dynamic Replace to miss those replacements, however the Transpose and Join approach triggers an Error Warning if this occurs which offers a nice proof for the issue. 

Thanks again @danilang!

[iCFO]

I spoke too soon. Once a value is changed for a given field name, this formula approach fails to replace any additional changed values that share the same field name. I believe this is why they approached this with an iterative macro here. Every row needs to be tested in a different iteration to avoid the problem...

https://community.alteryx.com/t5/Alteryx-Knowledge-Base/Tool-Mastery-Dynamic-Replace/ta-p/121655

I will take another shot at the iterative macro thing, but at this point I am starting to think that it is better to just build this out using a more complex join based workflow. I may be trying to make this tool something it is not. It is called "Dynamic Replace", but it doesn't appear to be designed to perform a straight forward dynamic replacement of string values.