Add missing data into table in script

[jozisvk11]

Hello,

I have a problem with connection of 2 tables.

Table 1 - from ODBC

Date	Workplace	Worked hours per day
1.3.2023	A	10
1.3.2023	B	11
2.3.2023	A	9
2.3.2023	B	10
3.3.2023	A	9
4.3.2023	B	10
5.3.2023	A	11
5.3.2023	B	10

Table 2 - from ODBC

Workplace	Day capacity
A	12
B	12

I need to add empty date to table 1. Date 3.3.2023 is missing Workplace B and 4.3.2023 is missing Workplace A. Then I need connect Table 1 and Table 2 with key field "Workplace".

So I need Table 1 with the following data:

Date	Workplace	Worked hours per day
1.3.2023	A	10
1.3.2023	B	11
2.3.2023	A	9
2.3.2023	B	10
3.3.2023	A	9
3.3.2023	B	0
4.3.2023	A	0
4.3.2023	B	10
5.3.2023	A	11
5.3.2023	B	10

Could you help me, how can I do this ???

Thank you.

Best regard.

[Sabrina_V]

Hello,

You can generate a master calendar between two dates  : link

Then do this : 

Concatenate(My table)

Load

<Mydate> as Date,

'A' as Workplace,

0 as Worked hours per day

RESIDENT MasterCalendar

Where not exists (Date,<Mydate>)

Concatenate(My table)

Load

<Mydate> as Date,

'B' as Workplace,

0 as Worked hours per day

RESIDENT MasterCalendar

Where not exists (Date,<Mydate>)

[vinieme12 · Accepted solution]

Try below

T1:
load * inline [
Date,Workplace,Workedhoursperday
1.3.2023,A,10
1.3.2023,B,11
2.3.2023,A,9
2.3.2023,B,10

3.3.2023,A,9
4.3.2023,B,10
5.3.2023,A,11
5.3.2023,B,10
];

temp_T2:
Load * inline [
Workplace,Daycapacity
A,12
B,12
];

Left Join(temp_T2)
Load FieldValue('Date',RecNo()) as Date
AutoGenerate FieldValueCount('Date');

Right Join(T1)
Load * Resident temp_T2;
Drop table temp_T2;