How to display "Unknown" for missing dates

[qlikmsg4u]

Hi Members,

I am new to qlikview, i want to know if there is any way to display "Unknown" for any missing date.

ex: in my table i have two fields

name,  dob

a,5/12/1990

b,9/02/1995

c,

d,

if i want to show dob unknown for c and d in a list box how can i achieve this ?

[Anonymous]

instead of DOB in listbox use formula

if (len(dob)<1,'unknown',dob) and it works

[MayilVahanan]

Hi

Try like this

Load name, If(Len(Trim(dob)) = 0, 'Unknown',dob) as dob Inline

[

name,  dob

a,5/12/1990

b,9/02/1995

c,

d,

];

[qlikmsg4u]

Thanks Manish it's working

[qlikmsg4u]

why i'm getting only "Unknown" in list box, when i run this script?

Load name, If(Len(Trim(dob)) = 0, 'Unknown',dob) as dob ;

Load

  name,

  Date(Date#(dob,'M/DD/YYYY')) as dob;

load name,

     dob

FROM

[..\New Microsoft Office Excel Worksheet.xlsx]

(ooxml, embedded labels, table is Sheet1);

[MK_QSL]

Provide your dob list

[qlikmsg4u]

simple excel sheet

[MK_QSL · Accepted solution]

DATA:

LOAD

  name,

  if(LEN(TRIM(dob))=0 or IsNull(dob), 'Unknown',dob) as dob;

LOAD

  name,

    Date(dob) as dob

FROM

[New Microsoft Office Excel Worksheet.xlsx]

(ooxml, embedded labels, table is Sheet1);

[qlikmsg4u]

working fine thanks Manish