Concatenar se determinado campo é nulo

[Listmaier]

Boa tarde,

Preciso de uma super ajuda!!!

Estou concatenando três tabelas mas a última somente posso concatenar se o campo "End Date" estiver nulo.

Muito obrigado pela ajuda!

LOAD
AutoNumber("Project ID"&"Package ID"&"Activity ID"&"Activity Name") AS ID_TIMELINE,
"Activity ID" AS #_ORDER,
"Project Name" as DES_PROJ,
"Activity ID" & ' - ' & Upper("Activity Name") AS DES_ACTIVITY,
// "Activity ID" & ' - ' & Upper("Activity Name")& ' - ' &"Package ID" AS DES_ACTIVITY,
'Plan' AS COD_CENARIO,
"Start Date (P)" AS DAT_START,
"End Date (P)" AS DAT_END

Resident [FACT_TEMP];

Concatenate
LOAD
AutoNumber("Project ID"&"Package ID"&"Activity ID"&"Activity Name") AS ID_TIMELINE,
"Activity ID" AS #_ORDER,
"Project Name" as DES_PROJ,
"Activity ID" & ' - ' & Upper("Activity Name") AS DES_ACTIVITY,
// "Activity ID" & ' - ' & Upper("Activity Name")& ' - ' &"Package ID" AS DES_ACTIVITY,
'Actual' AS COD_CENARIO,
"Start Date" AS DAT_START,
"End Date" AS DAT_END
Resident [FACT_TEMP];

If(IsNull("End Date"),
Concatenate
LOAD
AutoNumber("Project ID"&"Package ID"&"Activity ID"&"Activity Name") AS ID_TIMELINE,
"Activity ID" AS #_ORDER,
"Project Name" as DES_PROJ,
"Activity ID" & ' - ' & Upper("Activity Name") AS DES_ACTIVITY,
// "Activity ID" & ' - ' & Upper("Activity Name")& ' - ' &"Package ID" AS DES_ACTIVITY,
'Delivery %' AS COD_CENARIO,
"Start Date" AS DAT_START,
"End Date" AS DAT_END
Resident [FACT_TEMP];
))

[afurtado]

Nos LOADs e selects podes usar o Where

No seu caso seria colocar

Resident [FACT_TEMP] Where IsNull("End Date");

LOAD
    AutoNumber("Project ID"&"Package ID"&"Activity ID"&"Activity Name") AS ID_TIMELINE,
    "Activity ID" AS #_ORDER,
    "Project Name" as DES_PROJ,
    "Activity ID" & ' - ' & Upper("Activity Name") AS DES_ACTIVITY,
    // "Activity ID" & ' - ' & Upper("Activity Name")& ' - ' &"Package ID" AS DES_ACTIVITY,
    'Plan' AS COD_CENARIO,
    "Start Date (P)" AS DAT_START,
    "End Date (P)" AS DAT_END
Resident [FACT_TEMP];

Concatenate
LOAD
    AutoNumber("Project ID"&"Package ID"&"Activity ID"&"Activity Name") AS ID_TIMELINE,
    "Activity ID" AS #_ORDER,  
    "Project Name" as DES_PROJ,
    "Activity ID" & ' - ' & Upper("Activity Name") AS DES_ACTIVITY,
    // "Activity ID" & ' - ' & Upper("Activity Name")& ' - ' &"Package ID" AS DES_ACTIVITY,
    'Actual' AS COD_CENARIO,
    "Start Date" AS DAT_START,
    "End Date" AS DAT_END
Resident [FACT_TEMP];

Concatenate
LOAD
     AutoNumber("Project ID"&"Package ID"&"Activity ID"&"Activity Name") AS ID_TIMELINE,
    "Activity ID" AS #_ORDER,
    "Project Name" as DES_PROJ,
    "Activity ID" & ' - ' & Upper("Activity Name") AS DES_ACTIVITY,
    // "Activity ID" & ' - ' & Upper("Activity Name")& ' - ' &"Package ID" AS DES_ACTIVITY,
    'Delivery %' AS COD_CENARIO,
    "Start Date" AS DAT_START,
     "End Date" AS DAT_END
Resident [FACT_TEMP] Where IsNull("End Date");

[afurtado]

somente cuidar se de fato o campo data está nulo (sem nada) ou tem uma data tipo 01-01-0001, porque neste caso não funcionaria o isnull() (por razões obvias). Dai poderia usar um len(Data) > 0 ou year(Data) > 1900 ou outra alternativa