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
Highlighted
Hoowling
New Contributor III

Linking two tables with key when values are missing

Hi community, 

Need help with an issue that's driving me crazy. 

I have two tables where scrap data and used quantity is stored: 

clipboard_image_0.png

As you can see, I do not have scrap occurencies every month, 201904 and 201907 there were no scrapping done. 
The link between the tables is Location&YearMonth (Key) - The two tables cannot be joined, this is just a simplied example of my issue. 

The resulting table looks like this: 

clipboard_image_1.png

As you can see, the data for 201904 and 201907 is not linked between the tables as there where no scrap in those months. 

The expected result is this: 

clipboard_image_2.png

How do I solve this problem? 

//Hoowling


Labels (2)
4 Replies
Highlighted
Partner
Partner

Re: Linking two tables with key when values are missing

Why dont you left join scrapped quantity table to Used quantity table? (Assuming Used quantity will always have data)
fact:
load
yearmonth
,location
,usedquantity
from xyz;
left join(fact)
load
yearmonth
,location
,scrappedquantity
from abc;
Highlighted
Partner
Partner

Re: Linking two tables with key when values are missing

Hi,

if you use simple join, you'll never lose rows

Highlighted
MVP & Luminary
MVP & Luminary

Re: Linking two tables with key when values are missing

It's quite simple if you don't joined or associate the tables else just concatenate (respectively union in sql) them. This could be done directly which creates an asynchron table or you could put both quantities in a single field and adding another field which contained the value of 'Scrapped' respectively 'Used' to be able to differ your quantities.

- Marcus

Highlighted
Digital Support
Digital Support

Re: Linking two tables with key when values are missing

I know you said you did not want to Join things, here is a Design Blog post that could help I think:

https://community.qlik.com/t5/Qlik-Design-Blog/Don-t-join-use-Applymap-instead/ba-p/1467592

Couple others that might be handy as well:

https://community.qlik.com/t5/Qlik-Design-Blog/To-Join-or-not-to-Join/ba-p/1463102

https://community.qlik.com/t5/Qlik-Design-Blog/Concatenate-vs-Link-Table/ba-p/1467569

Regards,
Brett

To help users find verified answers, please don't forget to use the "Accept as Solution" button on any posts that helped you resolve your problem or question.