VLookup / Find Replace Tool with wildcards

[Shane76]

I've got some Source data with 3 fields:

...and a lookup file with 3 corresponding fields that I want to match to bring in the 2 TEST_FLAG fields:

From the two I can easily create a key. The challenge is that whereas in the Source data all 3 fields are populated with real values, in the lookup we have wildcards * that represent any value.

For example in the lookup TAX_KEY value 431_*_* should join to Record 2 in source i.e. value 431_12345_123 or indeed any record that starts 431_

In Looksups the first part of the key will always have a real value, but the second and final parts may either be wild or real.

[AngelosPachis]

Hi @Shane76 ,

Assuming I understand the requirements correctly, the find and replace tool will allow you to look for a certain string at any part of another string, so that should help you overcome the wildcard issue.

I've transposed all 3 source data fields into a single column and looked for those values at any part of the Tax Key column.

Hope that's what you're after, let me know if that works.

Cheers,

Angelos

[Shane76]

Hi Angelos thanks for the reply but I'm not really sure what you've done there. What I'm after is something like the below but as you can see if I use a Find Replace Tool I am only getting the exact match on Record 1 not a wildmatch on Record 2 for example:

[AngelosPachis]

Hi @Shane76 ,

Maybe you need to use a join tool after the Find & Replace tool, to bring the table in the format that you're looking for?

For example in the results pane above, I can see that Record 1 has matched based on the GL_PRODUCT_CODE_src_1 column (the value of which is 000) to the Tax key 000_51320_* and 000_56002_* etc. and you get the test flag columns back.

Maybe that's what you're after?

[Christina_H · Accepted solution]

I think the problem here is that @Shane76 is looking for the original 5 records with matched test flags.  Try this one instead - it relies on the product code never being a wildcard for the initial join.

[Shane76]

I'm not following what you're doing with the Name field at all but that gives 261 records when I'm expecting 6. The following highlighted in Red do not match yet they are being joined. 

[Shane76]

Thank you Christina. That gives the correct results. I'm going to go away and try to understand why now. THANX!

[AngelosPachis]

@Shane76 if @Christina_H solution answers your question, please don't forget to mark it as a solution to close this thread 🙂