Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Not applicable

Where clause using a date field

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 ;

1 Solution

Accepted Solutions
MVP
MVP

Re: Where clause using a date field

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

2 Replies
MVP
MVP

Re: Where clause using a date field

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

IAMDV
Honored Contributor II

Re: Where clause using a date field

Hi Miguel,

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

Cheers - DV

Community Browser