topic Re: Left-join two loaded tables in Connectivity & Data Prep

Left-join two loaded tables

jakobjensen — Wed, 05 Jan 2022 15:00:14 GMT

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"

Re: Left-join two loaded tables

maxgro — Wed, 05 Jan 2022 19:06:03 GMT

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

Re: Left-join two loaded tables

rwunderlich — Thu, 06 Jan 2022 04:15:17 GMT

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

Re: Left-join two loaded tables

jakobjensen — Thu, 06 Jan 2022 06:52:27 GMT

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?

Re: Left-join two loaded tables

jakobjensen — Thu, 06 Jan 2022 12:49:02 GMT

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"

Re: Left-join two loaded tables

rwunderlich — Thu, 06 Jan 2022 13:27:27 GMT

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