Join the same field from two different source tables

[qlikwiz123]

I have three tables

TableA:

Customer ID

TableB:

Customer ID

Indicator

TableC:

Customer ID

Indicator

I am doing a Left Join on TableA with TableB to join 'Indicator' Field. Not every Customer ID has Indicator values in TableB and which is why I want to do another left join over TableC, which contains those missing 'Indicator' values. Since the field names are same (Indicator), the 2nd left join fails to add the missing values.

How do I achieve this, with 'Indicator' values from TableB and TableC in a single field?

[Or]

There's probably a shorter form for this, but off the top of my head:

Table A:

Load Customer ID

From TableA;

TableTemp:

Load

Customer ID

Indicator

From TableB;

CONCATENATE

Load

Customer ID

Indicator

From Table C;

Left Join(TableA)

Load

Customer ID

Indicator

Resident TableTemp;

Drop Table TableTemp;

[Saravanan_Desingh]

Yes, this code should work. Table B & C should be concatenated before joining with A.

[marcus_sommer · Accepted solution]

I think the suggestion of concatenating at first the joining tables goes in the right direction but this concatenating should be done with a DISTINCT statement because otherwise there may be duplicates in the key-values which would increase the number of records.

Probably better would be to use mappings instead of a join approach. Mappings have no risk to change the number of records and are more flexible in many ways, for example to define an order from which table the match-value should come from at first and/or to set a default-value for non-matching keys. This may look like this:

mapB: mapping load * from TableB;
mapC: mapping load * from TableC;

TableA: load *, applymap('mapB', [Customer ID], applymap('mapC', [Customer ID], 'myDefault')) as Indicator
from TableA;

- Marcus

[jochem_zw]

first create a table D which is table B and a concat of table C. Drop table B and C, and do the left join of table D on table A

[merceris]

When our data was all in a single table, we could easily retrieve a particular paygonline row from that table in order to get all the pertinent data we needed for a particular query. For example, looking at our unnormalized table below, if we wanted information on the books that 'John Smith' has checked out.

[qlikwiz123]

Thank you all. This is really helpful