topic Re: handling Null values in Date field in QlikView

handling Null values in Date field

suryaa30 — Thu, 24 Aug 2017 13:38:19 GMT

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?

Re: handling Null values in Date field

felipedl — Thu, 24 Aug 2017 13:52:32 GMT

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.

Re: handling Null values in Date field

its_anandrjs — Thu, 24 Aug 2017 13:55:48 GMT

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

Re: handling Null values in Date field

suryaa30 — Fri, 25 Aug 2017 12:47:40 GMT

Thank you Felip

Re: handling Null values in Date field

felipedl — Fri, 25 Aug 2017 12:58:11 GMT

Glad it helped Surya