Linking 2 data tables using wildmatch / certain part of string

RWilliams92 — Wed, 03 Jul 2019 12:50:48 GMT

I'm struggling to link up 2 data tables within the data load.

What i'm looking to do is match up these 2 sources through a common field (Circuit ID). The issue I have with this is that the strings in each table can vary; they won't be like for like but will contain a common part within the string.

For example, in Table1, I could have 'AAA/BBB/LE-123456' and in Table2 I could have 'CEASE AAA/BBB/LE-123456'. I have used Wildmatch to at least ask if both fields have a string in a format (???/???/LE-??????). How would I go about inputting script to match on just that format, therefore ignoring any text that may proceed the required string? The text in front (or behind) could be of any length as it's manually input so I wouldn't be able to use LEFT or MID for example.

Desired output would be like below: (matching on the part of string in bold and ignoring other text)

Table1 (Circuit ID)	Table 2 (Circuit ID)
AAA/BBB/LE-123456	CEASE AAA/BBB/LE-123456
CPM: OOO/OOO/E1P123456	OOO/OOO/E1P123456 CAV
CLT;A NMA/POL/LE-109109	NMA/POL/LE-109109

Is this possible? Currently I have a bit of script:

If(WildMatch("CIRCUIT ID",'***/***/LE-******')=1 or WildMatch("CIRCUIT ID",'***/***/E?P******')=1 ...

but i'm now stuck on how i'd then get them to match between the 2 tables.

Thanks in advance

Ryan

Re: Linking 2 data tables using wildmatch / certain part of string

neelamsaroha157 — Wed, 03 Jul 2019 13:36:20 GMT

You may try this -

Re: Linking 2 data tables using wildmatch / certain part of string

RWilliams92 — Wed, 03 Jul 2019 15:33:20 GMT

Thank you, that's really helpful. Just trying to work out how to manipulate it to fit in with my data sources and just understand exactly what the code is doing.

topic Re: Linking 2 data tables using wildmatch / certain part of string in App Development

Linking 2 data tables using wildmatch / certain part of string

Re: Linking 2 data tables using wildmatch / certain part of string

Re: Linking 2 data tables using wildmatch / certain part of string