
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
NoConcatenate / Temporary Table / Join Tables
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;
Accepted Solutions

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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)
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.

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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)
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.

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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.

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
you will get all of tb1 plus data matched from tbl2

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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?

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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'
]
;

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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;

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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.

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Thanks for your advice. Will do.
Have a good day.
