Where clause using a date field

[#1]

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 ;

[Miguel_Angel_Baeyens · Accepted solution]

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]

Hi Miguel,

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

Cheers - DV