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
