Join when missing data

[Qliksense_77]

Hi,

I have data like below.

Tabel 1 is a large tabel when all the data except the number of beds.

I want to add  the number of beds that each department on specific dates to tabel 1.

I have tried using applymap with a key made from department&'-'&date. But the problem is that on some days, there are departments that have no patients.  (like department "a" on the 02/01/2022 in the example below)

How can I add a row to table 1 with the number of beds for the days, when there are no patients?

Tabel 1:
Department	Date	Number of patients
a	01-01-2022	2
b	01-01-2022	3
c	01-01-2022	4
b	02-01-2022	4
c	02-01-2022	5
Tabel 2:
Department	Date	Number of beds
a	01-01-2022	2
b	01-01-2022	2
c	01-01-2022	2
a	02-01-2022	2
b	02-01-2022	2
c	02-01-2022	2
Desired output:
Department	Date	Number of patients	Number of beds
a	01-01-2022	2	2
b	01-01-2022	3	2
c	01-01-2022	4	2
a	02-01-2022	0	2
b	02-01-2022	4	2
c	02-01-2022	5	2

[JHuis · Accepted solution]

You have to use concatenate tabel:

or you could use joins, but concatenate would do the trick. good luck!

Tabel1:
Load
Department,
Date,
Numberofpatients
From Tabel1 ;

Concatenate (Tabel1)
Load
Department
Date
Numberofbeds
from Tabel2;

[Qliksense_77]

@JHuis , thanks 😀