topic Re: len & isnull don't work correctly in QlikView

len & isnull don't work correctly

Wed, 28 Jan 2015 09:34:40 GMT

Dear QV users,

I have a date field in a QVD file. I have used the isnull en len fuction as you can see below. The only result I get is that it recognizes the field with date and gaves me a "NO" result. When a date is not filled it does not put yes on the field StatusLimisExWork (see below). When I dump the table to excel it has no data on field pj_leverdatum.

LimisData:
Load
ord_ext_ordnr as JOB_NUMBER,
pj_leverdatum as LimisExWork,

IF(len(trim(pj_leverdatum))='','YES','NO') as StatusLimisExWork,
FROM
[..\Data\QVD\Salesprognose.qvd]
(qvd);

Can someone help me please with this :-).

With kind regards,

Aissam

Re: len & isnull don't work correctly

Wed, 28 Jan 2015 09:37:04 GMT

Hi Aissam,

Len needs to be given a number to evaluate to, so use like the below

IF(len(pj_leverdatum)<1','YES','NO') as StatusLimisExWork

hope that helps

Joe

Re: len & isnull don't work correctly

Anonymous — Wed, 28 Jan 2015 09:41:15 GMT

USe like this

IF(Isnull(pj_leverdatum),'YES','NO') as StatusLimisExWork

Re: len & isnull don't work correctly

jagan — Wed, 28 Jan 2015 09:46:10 GMT

Hi,

Len() will return the number of characters in a field, so you have to use = 0

LimisData:
Load
ord_ext_ordnr as JOB_NUMBER,
pj_leverdatum as LimisExWork,

IF(len(trim(pj_leverdatum)) = 0, 'YES','NO') as StatusLimisExWork,
FROM
[..\Data\QVD\Salesprognose.qvd]
(qvd);

Regards,

Jagan.

Re: len & isnull don't work correctly

Wed, 28 Jan 2015 10:06:19 GMT

Dear users,

After using you scripting I still get the following results :-(.. (See table attached)

regards,

Aissam

Re: len & isnull don't work correctly

Anonymous — Wed, 28 Jan 2015 10:19:56 GMT

Hi,

Please find the attached application,

This will give u the complete information

Re: len & isnull don't work correctly

sunilkumarqv — Wed, 28 Jan 2015 10:34:48 GMT

Tru like this

IF(IsNull(AutoNumber(LimisExWork)),'Yes','No') as StatusLimisExWork

check attached @

Re: len & isnull don't work correctly

jagan — Thu, 29 Jan 2015 02:21:15 GMT

Hi,

Check this script, used your excel file and below script works fine

Directory;

LOAD JOB_NUMBER,

LimisExWork,

If(Len(Trim(LimisExWork)) = 0, 'Yes', 'No') AS Status

FROM

[15-0128 tabel results qv.xls]

(biff, embedded labels, table is Sheet1$);

Regards,

Jagan.