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!

[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

[Blake]

Hi @hal_dal - I might be misunderstanding your question but it seems like you just need to use a join to find your matching Project IDs from Table1 and Table2. 

Am I oversimplifying it? 

[fmvizcaino · Accepted solution]

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

[hal_dal]

@Blake 

Edit:the join is getting me almost to where I need, however, I'm still unsure of how I would deselect the other fields that I don't need?

(what i mean is, if I'm selecting April this month, I don't want Jan, Feb, May, etc. to be selected and to show at the end and dont want to do it manually)

...

Hi, I may be overthinking this as well.

But I would need to pass other fields with a bunch of other records that I cannot lose (I didn't include them in here)

and I would need to select only that one month.

So if I do a Join, I'm not sure how I would still specify that I need to select the April field for example? and when running this next month, I need to seelct May field and so on.

Hope that's clear.

Thanks

[hal_dal]

@fmvizcaino 

sorry, I didn't see your response!

Let me have a look at this and will get back to you both.

Edit:

I'm getting closer now but still facing the same issue of not knowing how to only select that specific month's field?

what I mean is, I don't want to manually deselect the fields but want to make sure that the final output only shows the current month;s field and not the other months.

I'm not sure if that's even possible?

Thanks a lot!

[Blake · Accepted solution]

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

[hal_dal]

@Blake @fmvizcaino 

thanks so much to both!

I have used a combination of both your answers and I finally was able to achieve it 🙂

I didn;t know dynamic select exist and never knew how to use it!