community
cancel
Showing results for 
Search instead for 
Did you mean: 

Alteryx designer Discussions

Find answers, ask questions, and share expertise about Alteryx Designer.
Upgrade Alteryx Designer in 10 Steps

Debating whether or not to upgrade to the latest version of Alteryx Designer?

LEARN MORE
SOLVED

Dynamic replace for multiple rows

Alteryx Partner

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.

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.

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

Highlighted
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

 

Alteryx Certified Partner
Alteryx Certified Partner

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

An iterative macro will run through every record in a data stream and loop the records back through the module, repeating the process as many times as specified or until a condition is met. This video will review the creation and deployment of an iterative macro within an Alteryx module.
Alteryx Partner

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.

Alteryx Certified Partner
Alteryx Certified Partner

Try this one

Alteryx Partner

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