Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements
Make your voice heard! Participate in the 2020 Wisdom of Crowds® Survey. BEGIN SURVEY
Not applicable

Split joins on multiple tables

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;

Tags (2)
2 Replies
Highlighted
MVP & Luminary
MVP & Luminary

Re: Split joins on multiple tables

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))?


talk is cheap, supply exceeds demand
Highlighted
Not applicable

Re: Split joins on multiple tables

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