Skip to main content
Announcements
Qlik Introduces a New Era of Visualization! READ ALL ABOUT IT
cancel
Showing results for 
Search instead for 
Did you mean: 
jakobjensen
Contributor II
Contributor II

Left-join two loaded tables

With the following two tables:

 

 

LIB CONNECT TO 'database';

LOAD id,age

[data]:
SELECT id, age from table2;



LIB CONNECT TO 'db';

LOAD date,id;

[transactions]:
SELECT * from table1

 

 

how do I do a left join of "data" onto "transactions"? The documentation is , to be honest, not very usefull 

 

 

LIB CONNECT TO 'database';

LOAD id,age

[data]:
SELECT id, age from table2;



LIB CONNECT TO 'db';

LOAD date,id;

[transactions]:
SELECT * from table1;

[joined_data]:
SELECT * from table1 LEFT JOIN SELECT * from table2;  #<-- join

 

 

But I just get "no table table1"

 

 

1 Solution

Accepted Solutions
rwunderlich
Partner Ambassador/MVP
Partner Ambassador/MVP

Here is the difference between SQL Join and Qlik Join.

In SQL you join two or more tables to create a new table -- the result set. 

In Qlik there are only two tables involved, the target table and the source table (my terms).  Rows from the source table update rows in the target table.  There is no "new" table as there is in SQL. 

TargetTable is a table that has been created by a SQL or Load statement, prior to the Join.

Join (targetTableName)  Load  * From sourceTable;

If loading from a database, SQL is used instead of Load. (Load and SQL can be combined in a "preceding load", another topic).  So the above abbreviated example could be:

Join (targetTableName)  SQL Select  * From sourceTable;

Returning to the example:

LIB CONNECT TO 'db';

[transactions]:
SELECT date,id from table1

LIB CONNECT TO 'database';

Left Join (transactions)
SELECT id, age from table2;

Data from table2 is joined onto the Qlik "transactions" table. The only table you will see in the Data Model viewer is "transactions" and it should have three fields: date, id, age. 

The table created by  "SELECT id, age from table2" is a temporary (or anonymous) table that exists only long enough to feed the Join.

You can find some free foundational courses at https://learning.qlik.com/ as well on Udemy.

-Rob
http://www.easyqlik.com
http://masterssummit.com
http://qlikviewcookbook.com

 

View solution in original post

5 Replies
maxgro
MVP
MVP

// load the first table table2 in Qlik (data)

LIB CONNECT ....;

[data]:

load id, age;

SELECT......;

 

// load the second table table1 in Qlik (transactions)

LIB CONNECT ....;

[transactions]:

load date, id;

SELECT .........;

 

// left join in Qlik, Qlik syntax, the join is by id field

LEFT JOIN (transactions) 

LOAD id, age

RESIDENT data;

 

// drop the first table 

DROP TABLE data;

 

 

 

rwunderlich
Partner Ambassador/MVP
Partner Ambassador/MVP

If you load Transactions first you can do it like this:

LIB CONNECT TO 'db';

[transactions]:
SELECT date,id from table1

LIB CONNECT TO 'database';

Left Join (transactions)
SELECT id, age from table2;

-Rob
http://www.easyqlik.com
http://masterssummit.com
http://qlikviewcookbook.com

 

jakobjensen
Contributor II
Contributor II
Author

Is that then a join of Table1 onto Table2 or the other way round? And how do you then use the "LOAD" to select data from the joined table?

 

jakobjensen
Contributor II
Contributor II
Author

What is the outcome of this i.e what is the table-name of the joined data? I cannot see the joined table in the "data model overview"

rwunderlich
Partner Ambassador/MVP
Partner Ambassador/MVP

Here is the difference between SQL Join and Qlik Join.

In SQL you join two or more tables to create a new table -- the result set. 

In Qlik there are only two tables involved, the target table and the source table (my terms).  Rows from the source table update rows in the target table.  There is no "new" table as there is in SQL. 

TargetTable is a table that has been created by a SQL or Load statement, prior to the Join.

Join (targetTableName)  Load  * From sourceTable;

If loading from a database, SQL is used instead of Load. (Load and SQL can be combined in a "preceding load", another topic).  So the above abbreviated example could be:

Join (targetTableName)  SQL Select  * From sourceTable;

Returning to the example:

LIB CONNECT TO 'db';

[transactions]:
SELECT date,id from table1

LIB CONNECT TO 'database';

Left Join (transactions)
SELECT id, age from table2;

Data from table2 is joined onto the Qlik "transactions" table. The only table you will see in the Data Model viewer is "transactions" and it should have three fields: date, id, age. 

The table created by  "SELECT id, age from table2" is a temporary (or anonymous) table that exists only long enough to feed the Join.

You can find some free foundational courses at https://learning.qlik.com/ as well on Udemy.

-Rob
http://www.easyqlik.com
http://masterssummit.com
http://qlikviewcookbook.com