Create a data set from excel sheets using something similar to the Index formula in excel

Question

Dear Alteryx colleague,

Here is the problem for which I need help for:

In this example, I have attached an excel file that present:

In tab " Reference data", I have a reference in Column B (FA mapping.)

This reference is the combination  of several fields. These fields refer to columns with headers.

In column B, the formula looks like that : =[@[Legal Entity]]&""&[@[Account Type]]&""&[@Account]&""&[@[Sub-Account]]&""&[@Position]&""&[@[Security Type]]&""&[@[Security Type 2]]&""&[@[Current/Non-Current]]&""&[@[Others/Affiliated]]

So my reference in column B is the result of the formula above. The result would look like that :

*\Assets\10090 - Investments\EN S.R.L.\*\Shares in affiliated undertakings\*\Affiliated

Based on the result of this column B, I have a used an Index formula in excel that is searching something corresponding to this reference in the Tab "data to find".

The issue, I am facing is that by using the Join tool, it s not recognizing the sign * as a wildcard and it is not returning what I am looking for.

you can see in tab "Reference data", in column Q, what is the value return for reference B2.

My question is: what is the tool I should use in order to get the same value as in Q2 of tab "Reference data" that comes from tab "Data to find ".

thanks for your help,

Guillaume

Alteryx question to the community.xlsx

KilianL · Accepted Answer

Hi @guigui17

If I understand your question correctly, you want to join data using wildcards.

e.g. *\ABC\*\100 should be joined with DEF\ABC\100

where the wildcard (*) can be anything

There is no tool to do this directly, so you need a workaround.

I can think of using the RegEx_Match function for this.

You can generate the RegEx pattern of the mapping and this allows matching based on wildcards.

Only drawback is that you need to multiply the rows of your datasets with the Append.

See an example attached.

I got the solution idea from here: https://community.alteryx.com/t5/Alteryx-Designer-Discussions/Regex-join-Wildcard-join-or-quot-Like-quot-Statements/td-p/33158

KilianL · Accepted Answer

Hi Gui,

Nice to see it working.

In your case, we need to escape more RegEx characters. For example a dot means 'any character' in RegEx, and brackets are used to define RegEx groups. By adding the escape character '\' in front, they are being treated as the characters themselves.

I changed this and now there are less unmatched records. I might have missed some RegEx characters or there is no match in the first place.