len & isnull don't work correctly

[#1]

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

[#2]

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

[Anonymous]

Hi

USe like this

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

[jagan]

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.

[#5]

Dear users,

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

regards,

Aissam

[Anonymous]

Hi,

Please find the attached application,

This will give u the complete information

[sunilkumarqv]

Tru like this

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

check attached @

[jagan]

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.