Dynamic replace for multiple rows

[david_pinsley]

I'm trying to apply multiple changes to a table based on data from another source.  The dynamic replace tool comes ever so close to delivering the functionality I'm looking for.  But it's not quite there.  

Consider this example, of an input file, with a single field called "phrase".

now is the time for all good men
i slit the sheet
the quick brown fox

And the need to do 2 things to EACH row, (1) add a period, and (2) title case the string... resulting in:

Now Is The Time For All Good Men.
I Slit The Sheet.
The Quick Brown Fox.

This example (module attached) demonstrates that the dynamic replace tool executes the replacement only for the first expression that evaluates to true (-1).  It does the first rule, appending a period "." to each phrase, resulting in this: 

now is the time for all good men.
i slit the sheet.
the quick brown fox.

But it never executes the next replacement, even though the expression is also true (-1).

I'd like to find a way for this to process ALL expressions that evaluate to true.

Why is this important?  My intention is to be able to let a group of data processors manage lists of rules outside of Alteryx - in flat files.  That way they can just update these "editing" rules.  I envision thousands of these editing rules, and such a facility would give us a great deal of flexibility on how to manage them.

Thank you for thinking about this.  Any feedback is most welcome.

David.

[RodL]

Just a thought, would it work for you to include the Dynamic Replace into a batch macro with your 'rules' file being the Control Parameter?

It would basically go through each of your rules (in the order of the file) and run the Dynamic Replace for each line regardless of what occurred previously.

[s_pichaipillai]

David,

Try the attached one

I just used TITLECASE and Padright for your issue using formula

TITLECASE(PadRight([phrase], Length([phrase])+1, '.'))

hope this helps

Thanks

Sar

[RobertBl]

Very clever use of the Dynamic Replace function - well done.

The reason it's not getting past the first replacement is that the Dynamic Replace by design just finds the first item in the replacement stream (R)  that evaluates to True and uses that and stops. So if every item in the replacement stream evaluates to True (as yours does) it will always take the first item.

As Rod says you could create a Batch Macro. By using your Rules File as the Control Parameter it would run each rule one at a time against the text. Each time in the batch it executes it would use one line from the Rules files and thus eventually apply all the rules to the text.

IF you haven't used a Batch Macro before there is a On Demand video on it on the training page. There are also examples under the Alteryx Help Menu

Help | Sample Workflows | Macro Samples

[michael_treadwell]

David,

A couple here have mentioned batch macros and I am not sure that is the way you want to go with this. The problem is that if you batch your data once per rule, you are going to get your data multiplied by N number of rules. Each batch will apply one and only one rule to each row. Your output would look something like this:

now is the time for all good men.
i slit the sheet.
the quick brown fox.

Now Is The Time For All Good Men
I Slit The Sheet
The Quick Brown Fox

What you actually need is to apply one rule to all the records and then loop those records back through the process. This is a job for an iterative macro. I have attached a workflow with an iterative macro that gives you the output that you desire. The only thing missing is a clever way to determine the iterations. In the macro I have uploaded, I set the iterations manually to 2 because that is the number of rules you have.

For more information about how to build these, see this video on iterative macros

[david_pinsley]

Hi Michael,

I'm so interested in seeing your solution.  Unfortunately, my company's still on version 9.5 and I can't open up the module you've sent.  Is it possible for you to save in a 9.5 compatible format?  I'm very grateful.

Best regards,

David.

[michael_treadwell · Accepted solution]

Try this one

[david_pinsley]

Thank you Michael.  Sorry for the delay acknowledging your solution.  I had to step away from the problem for a bit.  This does work as you describe.  Many thanks.