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

[guigui17]

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

[Hollingsworth]

@guigui17, it is unclear if your question relates to an Alteryx solution or an Excel solution.

You ask for which tool would work which suggests that you are trying to use Alteryx, but that is the only Alteryx-related information.

Are you trying to port this data transformation in Alteryx or are you looking for help to fix your Excel file?

[KilianL · Accepted solution]

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-...

[guigui17]

Hi Killian,

thanks a lot for your help. I have used your solution and it works for 70% of the cases.

I have analyzed the remaining cases that are there but I cannot identify what is the reason why these are not being picked up.

Please let me know what you may see as the remaining issue.

thanks a million,

[KilianL · Accepted solution]

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.