topic Re: Joins in QlikView

Joins

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

I have three tables. The Division table is joined by RegionData1 for the Data1 table and RegionData2 for the Data2 table. I do not want to see values for the Division table if the values do not exist in the data tables. I'm asusming a join is needed but not sure how to join.

Tables:

Division

Data1

Data2

*The example shown is in Excel but this is actually a SQL Script

Re: Joins

Anonymous — Wed, 22 Jun 2016 13:44:14 GMT

Hi mrJohn,

Load first Regions tables, and then Division with exists on where:

Directory;
LOAD *
FROM
Book1.xlsx
(ooxml, embedded labels, table is Data1);

Directory;
LOAD *
FROM
Book1.xlsx
(ooxml, embedded labels, table is Data2);

Directory;
LOAD *
FROM
Book1.xlsx
(ooxml, embedded labels, table is Division)
WHERE EXISTS(regionData1) or Exists(regionData2);

Regards!

Re: Joins

florentina_doga — Wed, 22 Jun 2016 13:46:08 GMT

try this

final:

LOAD *

FROM

Book1.xlsx

(ooxml, embedded labels, table is Division);

left join

LOAD *

FROM

Book1.xlsx

(ooxml, embedded labels, table is Data1);

left join

LOAD *

FROM

Book1.xlsx

(ooxml, embedded labels, table is Data2);

noconcatenate

final1:

load *

resident final where len(IdPK1)<>0 or len(IdPK2)<>0;

drop table final;

Re: Joins

sunny_talwar — Wed, 22 Jun 2016 13:47:51 GMT

What would you want the final output to look like?

Re: Joins

Anonymous — Wed, 22 Jun 2016 13:56:11 GMT

Essentailly a LEFT JOIN for both Data1 and Data2 to the Divisions Table.

Re: Joins

sunny_talwar — Wed, 22 Jun 2016 14:01:28 GMT

What the final output need to look like?

Like this?

1	East Central	East
2	East Central	East

Re: Joins

Anonymous — Wed, 22 Jun 2016 14:02:29 GMT

Correct