Multiple Find and Replace

[MohammedBurhaan]

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

[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. 

[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,

BS

[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! ] 😁