topic Re: Join the same field from two different source tables in QlikView

Join the same field from two different source tables

qlikwiz123 — Sat, 16 Nov 2024 00:39:51 GMT

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?

Re: Join the same field from two different source tables

Or — Fri, 15 May 2020 21:50:48 GMT

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;

Re: Join the same field from two different source tables

Saravanan_Desingh — Sat, 16 May 2020 03:42:29 GMT

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

Re: Join the same field from two different source tables

marcus_sommer — Sat, 16 May 2020 15:04:48 GMT

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

Re: Join the same field from two different source tables

jochem_zw — Sat, 16 May 2020 15:51:23 GMT

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

Re: Join the same field from two different source tables

merceris — Tue, 19 May 2020 16:43:57 GMT

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.

Re: Join the same field from two different source tables

qlikwiz123 — Mon, 18 May 2020 15:53:57 GMT

Thank you all. This is really helpful