topic Re: Filter table with date greater than within script in QlikView

Filter table with date greater than within script

Anonymous — Wed, 16 Dec 2015 09:28:41 GMT

Hi all,

I'm trying to load the past three months data from a QVD file, however I can't get my WHERE clause to work.

Here's my script:

Let v3MonthsAgoToday = AddMonths(Date(Today()),-3);

LOAD Audit_Id,

Date(Audit_Timestamp) AS Audit_Timestamp,

Value

FROM

Application_Audit.QVD (qvd)

WHERE Audit_Timestamp >= v3MonthsAgoToday;

I've tried surrounding both v3MonthsAgoToday and Audit_Timestamp with Date(), Date#() and Date([field/variable], 'dd-mm-yyyy') but none of these work. The only thing that works is WHERE Audit_Timestamp >= 42263, however I need this date to change relative to today's date. I'd rather get rid of the v3MonthsAgoToday variable if possible too.

Any responses much appreciated, thank you!

Re: Filter table with date greater than within script

rubenmarin — Wed, 16 Dec 2015 10:19:47 GMT

Hi Sarah, try with this variable value:

Let v3MonthsAgoToday = Num(AddMonths(Date(Today()),-3));

If doesn't works use the variable in where like:

WHERE Audit_Timestamp >= $(v3MonthsAgoToday);

Re: Filter table with date greater than within script

Wed, 16 Dec 2015 10:23:58 GMT

Answer above should work if the field in your where clause is formatted as a number and the variable is in date format.

However if this doesn't fix your issue could you post an simple app example.

Re: Filter table with date greater than within script

Anonymous — Wed, 16 Dec 2015 10:28:25 GMT

Thank you Ruben, I knew it would be something simple! Adding num() to the variable fixed it.

Re: Filter table with date greater than within script

Anonymous — Wed, 16 Dec 2015 10:29:56 GMT

try like this:

Let v3MonthsAgoToday = date(AddMonths(Today(),-3),'DD/MMM/YYYY');

LOAD Audit_Id,

Date(Audit_Timestamp,'DD/MMM/YYYY') AS Audit_Timestamp,

Value

FROM

Application_Audit.QVD (qvd)

WHERE Audit_Timestamp >= '$(v3MonthsAgoToday)';

Hope this will work!!

Re: Filter table with date greater than within script

rubenmarin — Wed, 16 Dec 2015 10:45:14 GMT

Yes, a date from qvd is also a number, so filter as number works.

The solution provided by Balraj also should work. Setting the date in the where clause between simple quotes keeps the string, without the simple quotes QV tries to resolve as a math operation making a division (with '/' as separator xx/xx/xxxx) or the difference (with '-' as separator xx-xx-xxxx).