topic Re: Linking two tables on partial data (like a WIldMatch) in QlikView

Linking two tables on partial data (like a WIldMatch)

dawgfather — Wed, 25 Nov 2020 16:16:04 GMT

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

Re: Linking two tables on partial data (like a WIldMatch)

marcus_sommer — Thu, 14 Mar 2019 10:06:42 GMT

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

Re: Linking two tables on partial data (like a WIldMatch)

dawgfather — Wed, 29 Jan 2020 16:54:03 GMT

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?

Re: Linking two tables on partial data (like a WIldMatch)

marcus_sommer — Thu, 30 Jan 2020 08:06:41 GMT

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