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