Filter table with date greater than within script

[Anonymous]

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!

[rubenmarin · Accepted solution]

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

[#3]

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.

[Anonymous]

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

[Anonymous]

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!!

[rubenmarin]

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