topic Re: Split joins on multiple tables in QlikView

Split joins on multiple tables

Wed, 12 Nov 2014 09:19:42 GMT

New to Qlik Sense and hoping for some help. I have two data connections and three tables I'm trying to join...

Connection 1 has Table 1 and Table 2

Connection 2 has Table 3

Table 1 has Field 1 (id) Field 2 (jobno) and Table 2 has Field 1 (id) and Field 2 (item)

Table 3 has Field 1 (jobno&item)

Joins:

Inner join from Table1 (id) to Table2 (id)

Left join from Table1 (jobno) to Table3 (jobno) and Table2 (item) to Table3 (item)

I have attempted to make the field formats the same and split the jobno and item in table 3 to make the join. Trying to get all results from table 1 and 2 and matching results by jobno and item from table 3, can anyone help?

LIB CONNECT TO 'CON1';

TABLE1:

LOAD ID,

UPPER(JOBNO) AS JOBNO,

SQL SELECT ID,

JOBNO,

FROM TABLE1;

INNER JOIN (TABLE1)

LOAD ID,

NUM(ITEM) AS ITEM;

SQL SELECT ID,

ITEM

FROM TABLE2;

LIB CONNECT TO 'CON2';

LEFT JOIN (TABLE1)

LOAD JOBITEM,

LEFT(JOBITEM,7) AS JOBNO,

NUM(MID(JOBITEM,7,3)) AS ITEM,

QTY;

SQL SELECT JOBITEM,

QTY

FROM TABLE3;

Re: Split joins on multiple tables

Gysbert_Wassenaar — Wed, 12 Nov 2014 09:58:05 GMT

You use the upper function in the first load statement on the JOBNO field. I'd use it in the last load statement to in order to insure those values will be in upper case too. UPPER(LEFT(JOBITEM,7)) AS JOBNO

I also noticed you use 7 as length in the LEFT function and as offset for the MID function too:

LEFT(JOBITEM,7) AS JOBNO,

NUM(MID(JOBITEM,7,3)) AS ITEM,

The last character of the JOBNO is also the first character of the ITEM? Or should it be NUM(MID(JOBITEM,8,3))?

Re: Split joins on multiple tables

Wed, 12 Nov 2014 10:23:13 GMT

Well spotted Gysbert, should have started MID from character 8. Thanks for help.