handling Null values in Date field

[suryaa30]

Hi All ,

I have an excel file with a date field. For other fields wherever there is blank I have used below expression so that I can select them from List box.

Make Blank values to Dummy variable:

IF(LEN(TRIM(FIELD))=0, 'UNSPECIFIED',FIELD) as FIELD

Now My question is how will apply something similar to above to a date field. I know one option would be to use a Dummy Date. Would it be possible I give the same Unspecified?

[felipedl · Accepted solution]

Hi Surya,

You could do something like this:

if(not isNum(Date),'UNSPECIFIED',date(Date,'MMM-DD-YYYY')) as Date

for the sample data provided in the xlsx file, it gives me the following

Felipe.

[its_anandrjs]

Yes you can try or else you can add any dummy date that recognise as dummy date

IF(LEN(TRIM(FIELD))=0, 'UNSPECIFIED',Date(FIELD)) as FIELD

[suryaa30]

Thank you Felip

[felipedl]

Glad it helped Surya