topic Re: Add missing data into table in script in App Development

Add missing data into table in script

jozisvk11 — Mon, 24 Apr 2023 05:35:46 GMT

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.

Re: Add missing data into table in script

Sabrina_V — Mon, 24 Apr 2023 07:50:32 GMT

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>)

Re: Add missing data into table in script

vinieme12 — Tue, 25 Apr 2023 06:36:38 GMT

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;