topic Re: Where clause using a date field in QlikView

Where clause using a date field

Sat, 05 Nov 2011 23:30:51 GMT

Hi All,

I’m trying to get the where clause in the script below to work. The CANCEL_DATE field will either have a value or be empty; I’m trying to load all the records where the CANCEL_DATE field is empty. Anybody knows to convert a date field to a number field?

LOAD

CANCEL_DATE,

ORD_Number,

ORD_REVENUE as TOTAL_ORDER_REV

Resident Data

Where num(CANCEL_DATE, '0.0' )= 0 ;

Re: Where clause using a date field

Miguel_Angel_Baeyens — Sun, 06 Nov 2011 01:47:04 GMT

Hi Ahmadh,

Try using the Len() function instead:

LOAD CANCEL_DATE,
     ORD_Number,
     ORD_REVENUE as TOTAL_ORDER_REV
RESIDENT Data
WHERE Len(Trim(CANCEL_DATE)) = 0;

There is a IsNull() function as well, but it doesn't work properly as far as I'm concerned in some x64 systems. The Len() as above will work just fine, and the Trim() function within will remove all leading and trailing blanks, if any.

Hope that helps.

Miguel Angel Baeyens

BI Consultant

Comex Grupo Ibérica

Re: Where clause using a date field

IAMDV — Tue, 08 Nov 2011 16:05:55 GMT

Hi Miguel,

I am very keen to know the reason for IsNull() not working in x64 systems? Thanks for your time.

Cheers - DV