Qlik Community

Ask a Question

New to Qlik Sense

If you’re new to Qlik Sense, start with this Discussion Board and get up-to-speed quickly.

Announcements
QlikWorld Online 2021, May 10-12: Our Free, Virtual, Global Event REGISTER TODAY
cancel
Showing results for 
Search instead for 
Did you mean: 
apthansh
Creator
Creator

Joining 2 tables

Hi - I have 2 tables below as an example.

Table 1:

ID      City

A        Atlanta

B       Boston

Table 2:

ID      City

A        Atlanta

D       Dayton

When I join I want the below results.As a result it should drop ID A, as it is in both the tables.Which join I should be doing for this ?

Output:

ID     City

B       Boston

D       Dayton

 

Labels (3)
1 Solution

Accepted Solutions
Nicole-Smith

The following code:

 

Table1:
LOAD ID as ID1, City as City1
INLINE [
	ID, City
	A, Atlanta
	B, Boston
];

Table2:
LOAD ID as ID2, City as City2
INLINE [
	ID, City
	A, Atlanta
	D, Dayton
];

Final:
LOAD ID1 as ID, City1 as City
RESIDENT Table1
WHERE NOT EXISTS(ID2, ID1);

CONCATENATE (Final)
LOAD ID2 as ID, City2 as City
RESIDENT Table2
WHERE NOT EXISTS(ID1, ID2);

DROP TABLES Table1, Table2;

 

 

Will produce the following output:

ID City 
BBoston
DDayton

View solution in original post

3 Replies
Nicole-Smith

The following code:

 

Table1:
LOAD ID as ID1, City as City1
INLINE [
	ID, City
	A, Atlanta
	B, Boston
];

Table2:
LOAD ID as ID2, City as City2
INLINE [
	ID, City
	A, Atlanta
	D, Dayton
];

Final:
LOAD ID1 as ID, City1 as City
RESIDENT Table1
WHERE NOT EXISTS(ID2, ID1);

CONCATENATE (Final)
LOAD ID2 as ID, City2 as City
RESIDENT Table2
WHERE NOT EXISTS(ID1, ID2);

DROP TABLES Table1, Table2;

 

 

Will produce the following output:

ID City 
BBoston
DDayton

View solution in original post

neelamsaroha157
Specialist II
Specialist II

You may also try this- 

Table1:
LOAD * INLINE [
ID, CityA
A, Atlanta
B, Boston
];

Table2:
LOAD * INLINE [
ID, CityB
A, Atlanta
D, Dayton

];


Temp:
NoConcatenate
LOAD Distinct ID as IDTemp Resident Table1;
Inner Join
LOAD Distinct ID as IDTemp Resident Table2;

Temp2:
NoConcatenate
LOAD Distinct ID, CityA as City Resident Table1; DROP Table Table1;
Outer Join
LOAD Distinct ID, CityB as City Resident Table2; DROP Table Table2;

Final:

NoConcatenate
LOAD * Resident Temp2 Where not Exists(IDTemp, ID); DROP Tables Temp, Temp2;

apthansh
Creator
Creator
Author

Awesomeness !