topic Re: How to display "Unknown" for missing dates in QlikView

How to display "Unknown" for missing dates

qlikmsg4u — Fri, 05 Sep 2014 08:13:05 GMT

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

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

Re: How to display "Unknown" for missing dates

Anonymous — Fri, 05 Sep 2014 08:19:19 GMT

in a listbox you can select under "formula" the symbols for null and missing values. there you can define "unknown"

in a Chart the same under presentation

Re: How to display "Unknown" for missing dates

deepakqlikview_123 — Fri, 05 Sep 2014 08:23:27 GMT

or u can simply use if (dob='','unknown',dob) as dob;

Re: How to display "Unknown" for missing dates

qlikmsg4u — Fri, 05 Sep 2014 08:24:18 GMT

i tried the thing replace - by "unknown" in null values but it is not displaying in list box.any suggestions

Re: How to display "Unknown" for missing dates

simondachstr — Fri, 05 Sep 2014 08:29:19 GMT

Try setting set NullDisplay='Unknown'; before loading the table (Haven't tested it).

Re: How to display "Unknown" for missing dates

qlikmsg4u — Fri, 05 Sep 2014 08:29:49 GMT

i tried this one also but no use..

here is my script..

LOAD name,

date(if(dob='','Unknown',dob),'MM/DD/YYYY') as dob1

FROM

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

Re: How to display "Unknown" for missing dates

qlikmsg4u — Fri, 05 Sep 2014 08:31:04 GMT

not working

Re: How to display "Unknown" for missing dates

Fri, 05 Sep 2014 08:32:15 GMT

hi ,

Try Like this ,

if (len(trim(dob))=0,'unknown',dob) as dob;

Thanks !!!

Re: How to display "Unknown" for missing dates

MK_QSL — Fri, 05 Sep 2014 08:32:33 GMT

Load

name,

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

Load

name,

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

Inline

[

name, dob

a, 5/12/1990

b, 9/02/1995

];

Re: How to display "Unknown" for missing dates

amit_saini — Fri, 05 Sep 2014 08:33:35 GMT

Try this:

=If( dob <>0, dob, ' Unknown')

Thanks,
AS

Re: How to display "Unknown" for missing dates

Anonymous — Fri, 05 Sep 2014 08:34:40 GMT

instead of DOB in listbox use formula

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

Re: How to display "Unknown" for missing dates

MayilVahanan — Fri, 05 Sep 2014 08:36:35 GMT

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

];

Re: How to display "Unknown" for missing dates

qlikmsg4u — Fri, 05 Sep 2014 08:52:18 GMT

Thanks Manish it's working

Re: How to display "Unknown" for missing dates

qlikmsg4u — Fri, 05 Sep 2014 09:03:29 GMT

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);

Re: How to display "Unknown" for missing dates

MK_QSL — Fri, 05 Sep 2014 09:07:23 GMT

Provide your dob list

Re: Re: How to display "Unknown" for missing dates

qlikmsg4u — Fri, 05 Sep 2014 09:10:41 GMT

simple excel sheet

Re: How to display "Unknown" for missing dates

MK_QSL — Fri, 05 Sep 2014 09:22:01 GMT

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);

Re: How to display "Unknown" for missing dates

qlikmsg4u — Fri, 05 Sep 2014 09:26:54 GMT

working fine thanks Manish