Matching key field in one table to two fields in another table

[cody_summers]

Good evening everyone,

I apologize if this seems like a simple question, but I was curious to see if anyone could provide me some insight into an issue I'm experiencing. I basically have two tables in report that I'm pulling in from a local database. Neither table has any field that matches in particular, but for the purposes of this report we're trying to compare the key field from one table to two separate fields in another table that "can" contain the same value. These fields have different names and a similar report was previously developed in Cognos (which we can't currently compare against since that particular Cognos report has been broken for years).

This is basically a GPS locating type report that prompts the user to select a possible value from the key field (called CD_LOC) and then their results should show a table that will bring up any corresponding values that match in either the END_LOC1 or END_LOC2 fields. These field values will match the CD_LOC value in all circumstances, but only one of the two may match (or both in many cases). With that said, neither END_LOC1 or END_LOC2 will have matching values when either one matches the CD_LOC field. Again, this may be a simple problem to solve and perhaps I'm overthinking its complexity, but I'm still new to this environment and am looking for any suggestions this community might be able to provide.

Thank you

[Oleg_Troyansky · Accepted solution]

Hi Cody,

this problem is not all that simple... In Qlik, database associations are always based on a single matching field between two tables. We can't facilitate an "EITHER OR" matching logic. So, you have a few options about addressing this problem:

1. Normalize your second table and bring it to the point where only one Location field exists. In the most simplistic way, you can load your data twice - once with END_LOC1 as END_LOC and the other time with END_LOC2 as END_LOC. Then you can associate END_LOC with the CD_LOC (by naming both fields with the same name)

2. Keep the tables separate and facilitate the "EITHER OR" logic using Set Analysis. This is a bit more complex and requires very careful processing. I wouldn't recommend that to a beginner.

Cheers,

Oleg Troyansky

Upgrade your Qlik skills with my book QlikView Your Business: An Expert Guide to Business Discovery with QlikView and Qlik Sense

[campbellr]

Hi Cody

My suggestion is to add a key to your data on load using the ApplyMap() function.

in the absence of table names from your post I'll make some up for my explanation:

Table 1, Data table with CD_LOC field

Table 2, Data table with END_LOC1 and END_LOC2

Use a mapping load of table 1 to contain CD_LOC as the first field, and a unique key from table 1 as the second field

Use applyMap function to add two possible keys to Table 2. then in a preceding load choose a key value, Assuming that each row in table 2 should only match to one row in table 1, the choice of key can be arbitrary. choose whichever one has a match.

eg:

Table1:

Load

     Key,

     CD_LOC,

     ......<Rest of fields>

from table1_source;

tbl1Map:

Mapping Load

CD_LOC ,

Key

Resident Table1;

Table2:

Load

     If(Key1<>'<No Match>',Key1,

           If(Key2<>'<No Match>',Key2,'<No Match>') as Key

    ,

     <Rest of fields>;

Load

     ApplyMap('tbl1Map', END_LOC1 ,'<No Match>') as Key1,

     ApplyMap('tbl1Map', END_LOC2,'<No Match>') as Key2,

     ....<Rest of Fields>

From table2_Source

Now Table 2 will have a field, Key that matches the field Key in table1

If you need further explanation please let me know.

Ron