How to join these two tables?

[#1]

Hi,

I have the following script:

Tsick: Load ID_ZKT,
DATUMINGANGZIEK AS DATIZIEK,
DATINGANGGEDHERSTELD AS DATEZIEK;
SQL SELECT *
FROM PRODPIM."PIM_VMI_ZKTACT";

TempSickData:
LOAD ID_PER,

     ID_ZKT,

     date(DATIZIEK,'DD-MM-YYYY') as DATIZIEK,

     if(IsNull(DATEZIEK), if(DATINGANGGEDHERSTELD = '0', Today(), date(DATEZIEK,'DD-MM-YYYY'))) as DATEZIEK
    
;
SQL SELECT *
FROM PRODPIM."PIM_TZKT";

Some of the DATEZIEK fields are empty. I want to fill these fields with the date in the column DATINGANGGEDHERSTELD where the ID_ZKT are matching. If the DATEZIEK field is empty and there is no match in the Tsick table i want to fill it with the current date, that would be today().

How can i join these two tables to make this work?

[#2]

Hi, try this script.

TempSickData:

LOAD ID_PER,

     ID_ZKT,

     DATEZIEK;

SQL

SELECT *

FROM PRODPIM."PIM_TZKT";

Tsick:

OUTER JOIN (TempSickData)

LOAD

          ID_ZKT,

          DATINGANGGEDHERSTELD;

SQL

SELECT *

FROM PRODPIM."PIM_VMI_ZKTACT";

NOCONCATENATE

Result:

LOAD

          ID_PER,

          ID_ZKT,

          DATE(IF(ISNULL(DATEZIEK) = -1, IF(ISNULL(DATINGANGGEDHERSTELD)= -1, TODAY()), DATEZIEK), 'DD-MM-YYYY') AS DATEZIEK

RESIDENT TempSickData;

DROP TABLE TempSickData;