Use Find and Replace with a dynamic field

hal_dal

Hi,

I'm struggling trying to figure out something.

I have a field that I need to use find and replace on and need to append a specific field using find and replace.

That specific field will change depending on the month.

I'm not sure how to append the field if it's always going to change monthly?

The data I have are sensitive but I can attempt to create a mock-up data if needed.

Thanks a lot!

Input

Reporting

Output

Tips and Tricks

Join

Accepted answers

fmvizcaino

Hi @hal_dal ,

I agree with @Blake about joining and I'm just complementing the answer with the dynamic rename of the column based on today's date. I left a formula tool calculating exactly what I'm trying to find inside the contains function.

Best,

Fernando Vizcaino

2020-04-15 Use Find and Replace with a dynamic field.yxmd

Blake

Use the Dynamic Select tool with the following expression :

[Name]= "ID" OR contains([Name],DateTimeFormat(DateTimeAdd(DateTimeNow(),1,"month"),"%b - %Y"))

BEFORE you join with your main data set so that you're only bringing the data you want through the Join. Feel free to check the formula by changing it to 2 months, 3 months, 1 month, etc. You might have to change the name of the ID field to match your data set or massage the function so that instead of contains it is equals with the dynamic date time sandwiched in.

If this does what you're looking for, please mark this as solved so others can find it in the future.

Stay safe,

Blake

haldal.yxmd

All comments

fmvizcaino

Hi @hal_dal ,

Would you be able to share that sample dataset? Also showing how it would change accross the months.

Best,

Fernando V.

hal_dal

Hello,

here is the below data.

Table1:

Project ID

000123

000333

Table2:

Project ID	Forecast (Jan - 2020)	Forecast (Feb - 2020)	Forecast (Mar - 2020)
000123	123.43	244323.2	2343
000333	23243.3	21334	532432

I basically need to use Find and Replace to find the matching project ID between the two tables.

Then append the correct Forecast field from table 2 depending on which month we're in (I have the logic to figure out the month we're in already)

Then after appending, I will have to rename that column.

My issue currently is trying to figure how I'd append the correct field with the changing months?

Hopefully that was clear,

thanks

Quick Links

This months top contributors

atcodedog05 19458

Qiu 15866

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703