Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
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;
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>)
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;