topic Re: VLookup / Find Replace Tool with wildcards in Alteryx Designer Desktop Discussions

VLookup / Find Replace Tool with wildcards

Shane76 — Mon, 20 Sep 2021 12:28:25 GMT

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.

Re: VLookup / Find Replace Tool with wildcards

AngelosPachis — Mon, 20 Sep 2021 12:39:00 GMT

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

Re: VLookup / Find Replace Tool with wildcards

Shane76 — Mon, 20 Sep 2021 12:48:28 GMT

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:

Re: VLookup / Find Replace Tool with wildcards

AngelosPachis — Mon, 20 Sep 2021 13:04:57 GMT

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?

Re: VLookup / Find Replace Tool with wildcards

Christina_H — Mon, 20 Sep 2021 13:08:53 GMT

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.

Re: VLookup / Find Replace Tool with wildcards

Shane76 — Mon, 20 Sep 2021 13:10:36 GMT

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.

Re: VLookup / Find Replace Tool with wildcards

Shane76 — Mon, 20 Sep 2021 13:18:37 GMT

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

Re: VLookup / Find Replace Tool with wildcards

AngelosPachis — Mon, 20 Sep 2021 13:41:12 GMT

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