Community Spring Cleaning week is here! Join your fellow Maveryx in digging through your old posts and marking comments on them as solved. Learn more here!

Alteryx Designer Desktop Discussions

Find answers, ask questions, and share expertise about Alteryx Designer Desktop and Intelligence Suite.
SOLVED

Dynamic replace for multiple rows

david_pinsley
7 - Meteor

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.

7 REPLIES 7
RodL
Alteryx Alumni (Retired)

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
12 - Quasar

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
Alteryx Alumni (Retired)

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
ACE Emeritus
ACE Emeritus

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
7 - Meteor

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
ACE Emeritus
ACE Emeritus

Try this one

david_pinsley
7 - Meteor

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.

Labels