Linking two tables on partial data (like a WIldMatch)

[dawgfather]

I have two sets of data where one set has a definitive piece of information and the other data set has a freeform field that I am trying  to "search" through and match if that definitive piece exists in that freeform field.  Normally I'd have two fields in my load sequence (table A's piece named "datapiece" and table B's piece also named "datapiece") and then QV links the two tables on that field. That works great if there is a perfect patch.

How can I link two tables on a partial match? I can't use a Mapping, as that would only pull in 1 answer/match.

Example:

Table A:

Field = "path" and is something like "folder1/subfolder7"

SystemName    Path

System1                folder1/subfolder2

System2                folder1/subfolder7

System3                folder2/subfolder2

Table B:   where that "path" might exist somewhere inside the "mount" field

Server         IP                             Mount

ServerA      10.10.10.10          "/etc dev nosuid filerA:/vol/folder1/subfolder7 /mnt"

ServerB     11.11.11.11          "filerB:/vol/folder1/subfolder7 /var"

ServerC      12.12.12.12          "filerM:/vol/folder1/subfolder2 /www nosuid RW RO"

ServerD      9.9.9.9                    "Local RO /etc/ora commonname:/vol/folder1/subfolder2 /www RW"

So in theory I could link the tables and come up with something like this since ServerA and ServerB both have the "folder1/subfolder7" in there and then ServerC and ServerD both have the "folder1/subfolder2" in there:

System     Path                                     Server           IP

System1   folder1/subfolder2       ServerC      12.12.12.12 

System1   folder1/subfolder2       ServerD      9.9.9.9 

System2   folder1/subfolder7       ServerA      10.10.10.10

System2   folder1/subfolder7       ServerB     11.11.11.11

System3   folder2/subfolder2       null              null

[marcus_sommer · Accepted solution]

You could use a mapping for it, for example:

map: mapping load Path, chr(1) & SystemName & chr(2) from TableA;

TableB:
load *, textbetween(mapsubstring('map', Mount), chr(1), chr(2)) as SystemName from TableB;

- Marcus

[dawgfather]

Marcus - this worked like a charm for the past 9 months...now I have an interesting thing happening.

We added a second/different set of systems so that "SystemName " is something I have to create with an IF statement...essentially IF systemtype=1 THEN foldertype1/subfolder OTHERWISE foldertype2/subfolder...as SystemName. And I get the proper data in this new SystemName field.

But when I go to put that SystemName field inside the "map: mapping load Path, chr(1) & SystemName & chr(2) from TableA;"...it only works for the first part of the IF statement. If my IF statement starts with Type=1 (IF systemtype=1, then...) then my Type 1 things match but the Type 2 don't. If I flip the IF statement to be "IF systemtype=2, THEN  foldertype2/subfolder OTHERWISE foldertype1/subfolder...as SystemName" then my Type 2 stuff works but not my Type 1.

I tried to create this SystemName in a SQL statement as part of the main load thinking I could do away with the IF statement using a CASE in the SQL query...but I get the same thing there...if Type 1 is first in my CASE statement, I get type 1 matches but no type 2. If Type 2 is first in my CASE statement, my type 2 stuff matches but type 1 does not.

Really strange and it makes no sense to me because the data in that SystemName field is right...I can copy that cell and then search for *SystemName* (copied) in that Mount field (second part of the process you answered) and it finds the matches.

Got any ideas?

[marcus_sommer]

I'm not sure that I comprehend it right what has changed / needs to be adjusted to get now the wanted results. I assume that you hadn't a logic-mistake within your if-loop so that the else-part would returned respectively the second condition could never be true because the first condition is always true.

Therefore I could imagine that it is caused from the mapping - and a mapping worked like a VLOOKUP in Excel and returned always the value from the first match regardless how many matches there are.

Depending on the kind of the data an appropriate ordering might be useful (by dates or id's from which only the latest should be taken or something similar). Another approach is to use an aggregation and to concat() all possible return-values - and afterwards you used an additionally subfield-logic to pick the right part from it. I use this logic with applymap() quite often to avoid multiple mappings. In your case with mapsubstring() it's probably not so easy to implement.

A further way to bypass this kind of challenge is to extend the lookup-value so that each value is definitely unique and then to add this extension also within the mapping-query - maybe with some if-loops. Also the use of several mappings might be helpful - just pulling all matching-values and an afterwards-logic choosed from them the right one. I think I would rather start with my last suggestion to see if it worked and if you could improve the elegance and performance later.

- Marcus