Multiple Find and Replace

Account No	Name	Email Subject Line	Desired Output_Email Subject Line
111	Jack	Hi [Name] - Your account is [Account No]	Hi Jack - Your account is 111
222	Rob	Hi [Name] - Greetings.	Hi Rob - Greetings.
333	Louis	Hi - Greetings.	Hi - Greetings.

Hi guys,

I would want a solution to the above, I have a field 'Email Subject Line' which might or might not need the data contained in other fields, I want a dynamic mechanism by which I can read the 'Email Subject Line' and just replace the fields of variables with exact variable values.

I would want a way to get that as show in the last column 'Desired Output_Email Subject Line'

Please note that, it can have any number of variables in it, sometimes 0 or sometimes 10 or any number of variables names of the available columns in it.

Please help

Thanks

Developer

Common Use Cases

Expression

Accepted answers

All comments

Gawa

hi @MohammedBurhaan

When you need to take care of dynamic number of columns, it is recommended to Transpose data into vertical shape first. By doing so, you will always have the same schema: [Name], [Value]

In your case, you also need to configure Multi Row Formula to do your task. Here is the WF for you reference.

1251323_gawa.yxmd

MohammedBurhaan

Hey,

I have added a new column as desired output, can you look into it and elaborate in more detail please?

Gawa

@MohammedBurhaan Did you check and run my WF in the above post? If not yet, please check it first, and tell me what you are not clear about!

BS_THE_ANALYST

. Trying to find another route

BS_THE_ANALYST

@MohammedBurhaan

Okay, this is my route.

It's dynamic and it should also be fast. Let me know your thoughts @gawa but I think its better than the multi-row due to the hardcoding. It's also nice as we're not iterating.

All the best,

community multi find replace v2.yxmd

MohammedBurhaan

Hey

Thanks, this looks easy and quick.

I would love to see the entire solution, but I cannot view it since I do not have an Alteryx License (my trial got over last week lol).

BS_THE_ANALYST

@MohammedBurhaan

So Alteryx has expired and you no longer have access to Alteryx? Not sure how I'm meant to help besides the solution I've provided.

If you don't have alteryx, just solve this problem in Python. ChatbotGPT it and you'll have it solved and sorted in 30 minutes tops. Probably won't be as efficient as this solution, it'll likely iterate over each of the columns in conjunction with .apply() to perform the replacement.

Failing that, download the workflow and open it in notepad++ to see the underlying XML and go crazy with it.

All the best,

Ben

Gawa

hi @BS_THE_ANALYST I like yours. It's another solution!

Just a tiny thing...but let me comment. If e-mail subject line contains some words enclosed by [ ], that are not intended to be replaced, your WF append record ID at top of the word inside [ ].

For example, see record ID=4 in the below snap shot. Original data had the word [Dummy] (it should appear as [Dummy] in a result), and it comes out as [4Dummy] in a result column. We need to take care of this exception somehow.

Needless to say, the best practice is to avoid using [ ] as a part of words, if we apply the logic that relies on the assumption "variables are always enclosed by [ ]".😅

flying008

Hi, @MohammedBurhaan

A dynamic solution for your information: [As @gawa said, If line of 'e-mail subject line' contains some words enclosed by [ ], but not exist in the fields name, then need more logic to pass it . but anyway, we can do it! ] 😁

录制_2024_03_16_16_16_47_883.gif

BS_THE_ANALYST

@flying008 forgive me if I'm wrong (I don't use and have never used the dynamic replace) but it seems like you're using the SWITCH function to manually target certain instances of [brackets] in the string. This is not at all dynamic if you need to declare the string you want to replace. My interpretation of dynamic is that the solution will take unseen things into account, still work, and still solve.

With that being said, if we used a field info tool in combination with a batch macro we could directly overwrite the formula configuration for the switch function so that it encompasses all current and future columns as the formula tool's configuration will be updated dynamically per run with any columns names. Failing that, we drop the formula tool as the switch function is nothing more than a alternative to find and replace and we just create a text input inside the batch macro with a find and replace?

@flying008 let me know if this idea is an improvement to your solution or I'm just talking crap and your solution already takes this into account. I'm a rookie with the dynamic replace. If your solution does already take this into account, please can you share your workflow so I can learn from it?

Yes, I'm assuming every instance of "[xyz]" in the text would require replacing with it's respective column if there is a column called "xyz" available. I think this is reasonable for a dynamic solution. Otherwise, manual targeted replacement which I assume you provided above will suffice.

All the best,

flying008

Hi, @BS_THE_ANALYST

I'm just using the Switch function here to let Dynamic Replace tool determine which field to match, not to manually locate the string, and the key to using the formula here is to convert the original string into a formula with references, which is the core of Dynamic Replace tool.

Of course, what is avoided here is the fact that the field name in [] does not exist, as @gawa said, and whether the CaseN of the Switch function has a hierarchical restriction similar to that of the Excel If function (e.g. 7 or 255), because the Alteryx help does not make this clear.

IMHO, it should be appropriate to use Dynamic Replace tool in this scenario, because it can be really replaced on the fly without manual positioning, and once the formula is created, the formula can be dynamically updated and output the corresponding result every time it runs, regardless of the increase or decrease in the name of the reference character column or the name change.

******

I'm sorry, because of the restrictions of the security policy, I can't upload any workflow files, so here is the animation for your reference.

录制_2024_03_18_09_16_05_345.gif

BS_THE_ANALYST

@flying008 thank you for showing the tool configuration, I can follow this perfectly. This is indeed dynamic, very cool stuff. I'll most certainly explore this today.

Every day is a school day 😎.

All the best,

MohammedBurhaan

Hey All,

Loved the response, and the enthusiasm from everyone.
I've got a bigger challenge here -

Please try to help me out on this - https://community.alteryx.com/t5/Alteryx-Designer-Desktop-Discussions/How-to-generate-email-with-a-table-in-it/m-p/1252339#M311922

flying008

Hi, @MohammedBurhaan

If can help you get your want, please mark it as a solution and give a like for more share.

Quick Links

This months top contributors

atcodedog05 19458

Qiu 15866

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703