Qlik Community

New to Qlik Sense

If you’re new to Qlik Sense, start with this Discussion Board and get up-to-speed quickly.

cancel
Showing results for 
Search instead for 
Did you mean: 
suvechha_b
Creator III
Creator III

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

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,

1 Solution

Accepted Solutions
suvechha_b
Creator III
Creator III
Author

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.

View solution in original post

2 Replies
segador_
Partner
Partner

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"



 

Regards, Igor.
suvechha_b
Creator III
Creator III
Author

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.