Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements
QlikWorld 2020: Join us May 11 - 14, 2020 in Phoenix, AZ. Register early and save $400. Learn More
Highlighted
sarah_lamb
New Contributor III

Filter table with date greater than within script

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!

1 Solution

Accepted Solutions

Re: Filter table with date greater than within script

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);

View solution in original post

5 Replies

Re: Filter table with date greater than within script

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);

View solution in original post

Not applicable

Re: Filter table with date greater than within script

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.

sarah_lamb
New Contributor III

Re: Filter table with date greater than within script

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

balrajahlawat
Esteemed Contributor

Re: Filter table with date greater than within script

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

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).