Dynamic replace for multiple rows

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.

dynamic replace question.yxmd

Preparation

Accepted answers

michael_treadwell

Try this one

dynamic_replace_question.yxzp

All comments

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

Titlecase.yxmd

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

dynamic_replace_question.yxzp

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.

Quick Links

This months top contributors

atcodedog05 19458

Qiu 15866

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703