NoConcatenate / Temporary Table / Join Tables

[Mike_Y]

Dear talents,

I have below script: two temp tables tb1 and tb2.

(I also attached my qvf file in the post for your reference.)

What I want to achieve:

I would like to join them up and store the final result into another temp table called result.

What I am after is like the attached screenshot:

I am able to get what I want but my confusions are:

1)

To get the desired result, I have to use NoConcatenate when joining up the table together.

I do not know WHY I have to do so.

If I do not store the data into a temp table, I do not need to use the NoConcatenate but still can get the satisfied result.

Do you know how does this work?

2)

If I use NoConcatenate + LEFT JOIN, I will not get the desired result.

Only NoConcatenate + OUTER JOIN can do.

I do not know why this happened as well.

Can you help me, please?

tb1:
Load * Inline
[
id,date,pro,points,slot_index
'men', '20190501', 'COL',1,1
'man', '20190501', 'GAS',2,2
'msn', '20190510', 'G/C',3,3
]
;

tb2:
Load * Inline
[
id,date,pro,points,name
'men', '20190501', 'COL',1,'abc'
'man', '20190501', 'FES',1,'bbc'
'xxx', '20190501', 'G/C',5,'cbc'
]
;

result:
NoConcatenate
Load
id,
date,
pro,
points,
slot_index
Resident tb1;

Outer Join

Load
id,
date,
pro,
points,
name
Resident tb2;

[dplr-rn · Accepted solution]

Couple of things
1- NoConcatente is to prevent result table from concatenating automatically back to tb1 (i.e. a separate table called result will not be formed)

check https://help.qlik.com/en-US/sense/April2019/Subsystems/Hub/Content/Sense_Hub/LoadData/concatenate-ta...

2- The join happens using a combination of these columns 

id,date,pro,points

Left join will not give you the result you want because the 'left' side i.e. tb1 take priority . tb2 has only 1 row with same combination as tb1 (the row with name abc). So with left join you will loose the other 2 rows in tb2. Its same as sql concepts

Also why do you need a separate table results (apart from tb1 and tb2)? you can outerjoin tb2 to tb1 directly to get a single table.

[Mike_Y]

Hi dili,
Thanks for your explanation.

Answer your question first.
I need to store the join result into a QVD file, therefore I will get a shared data source which can be accessed by several qlik applications.
Hope this makes sense to you.

Additional question to you:
If I use left join only but do not store result in a temporary table, I will get both matching and not matching records. Not like what you suggested “only the matching row abc”. In SQL, I got all records as well because it’s left join.

Can you please confirm your assumption?

Look forward to hearing from you.
Thanks.

[dplr-rn]

i meant you will get matching rows only from tbl2.
you will get all of tb1 plus data matched from tbl2

[Mike_Y]

Thanks dili for your prompt reply.

I actually can get all the not matching rows from the tb2 by using left join only.
Would you like to give it a try, please?

[dplr-rn]

result of this code

tb1:
Load * Inline
[
id,date,pro,points,slot_index
'men', '20190501', 'COL',1,1
'man', '20190501', 'GAS',2,2
'msn', '20190510', 'G/C',3,3
]
;
left join (tb1)
Load * Inline
[
id,date,pro,points,name
'men', '20190501', 'COL',1,'abc'
'man', '20190501', 'FES',1,'bbc'
'xxx', '20190501', 'G/C',5,'cbc'
]
;

[Mike_Y]

Hi Dili,

Thanks for your reply.

Please check out my outcome below.

I got five records return.

Below is my code:

Any comments?

tb1:
Load * Inline
[
id,date,pro,points,slot_index
'men', '20190501', 'COL',1,1
'man', '20190501', 'GAS',2,2
'msn', '20190510', 'G/C',3,3
]
;

tb2:
Load * Inline
[
id,date,pro,points,name
'men', '20190501', 'COL',1,'abc'
'man', '20190501', 'FES',1,'bbc'
'xxx', '20190501', 'G/C',5,'cbc'
]
;

Load
id,
date,
pro,
points,
slot_index
Resident tb1;

Left Join

Load
id,
date,
pro,
points,
name
Resident tb2;

[Mike_Y]

By the way, can you tell me how did you display the return data? (in which software?)

I can only use the Qlik straight table to view them which is a bit "time-consuming".

Thanks very much for your help again.

[dplr-rn]

i was displaying in qlikview. (personally i dont think its difficult in sense 🙂 )

in your code because your removed no concatenate you are effectively concatenating multiple tables (into tb1) and then doing the join.
if you look at data model viewer you will not get 3 tables which i believe you want you are getting 2. Issue is with auto concat i dont know if we can always trust the result. ( i am on the road so i cant check properly)

comment your code at each step and check data model output you will see the results

[Mike_Y]

Thanks for your advice. Will do.

Have a good day.