state 1900-01-01 00:00:00.000' as null in qliksence script ?

[suvechha_b]

Hi All,

How to write in qliksense script , if [POLICY_HOLDER_DATE_OF_BIRTH]='1900-01-01 00:00:00.000'  then say NULL() ?

[DWQMV_POLICY_HOLDER_ID_DETAIL]:
LOAD
"POLICY_HOLDER_KEY",
"POLICY_HOLDER_ID_DETAIL_KEY",
"POLICY_HOLDER_NAME",
"POLICY_HOLDER_FIRST_NAME",
"POLICY_HOLDER_LAST_NAME",
"POLICY_HOLDER_ID_NUMBER",
if([POLICY_HOLDER_DATE_OF_BIRTH]='1900-01-01 00:00:00.000',Null(),[POLICY_HOLDER_DATE_OF_BIRTH]) AS "POLICY_HOLDER_DATE_OF_BIRTH"
FROM 'lib://QlikData/Model_PolicyHolderIDDetails.qvd' (qvd);

Thanks,

[segador_]

Hi!

Your expression is correct:

if([POLICY_HOLDER_DATE_OF_BIRTH]='1900-01-01 00:00:00.000',Null(),[POLICY_HOLDER_DATE_OF_BIRTH]) AS "POLICY_HOLDER_DATE_OF_BIRTH"

Maybe the values of field POLICY_HOLDER_DATE_OF_BIRTH does not have'1900-01-01 00:00:00.000' value. For example, it could be zero values or values in another date format.

You may try to write the expression like this:

if(IsNum([POLICY_HOLDER_DATE_OF_BIRTH]) and Year([POLICY_HOLDER_DATE_OF_BIRTH]) = 1900,Null(),
if(IsText([POLICY_HOLDER_DATE_OF_BIRTH]) and Index([POLICY_HOLDER_DATE_OF_BIRTH],'1900-01-01') > 0,Null(),
[POLICY_HOLDER_DATE_OF_BIRTH])) as "POLICY_HOLDER_DATE_OF_BIRTH"

[suvechha_b · Accepted solution]

LOAD
"POLICY_HOLDER_KEY",
"POLICY_HOLDER_ID_DETAIL_KEY",
"POLICY_HOLDER_NAME",
"POLICY_HOLDER_FIRST_NAME",
"POLICY_HOLDER_LAST_NAME",
if([POLICY_HOLDER_DATE_OF_BIRTH]='1900-01-01 00:00:00.000',Null(),[POLICY_HOLDER_DATE_OF_BIRTH]) AS "POLICY_HOLDER_DATE_OF_BIRTH",
FROM 'lib://QlikData/Model_PolicyHolderIDDetails.qvd' (qvd);

This script worked.