Left-join two loaded tables

[jakobjensen]

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"

[maxgro]

// 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]

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]

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]

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 · Accepted solution]

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