Using a relative date where clause in a qvs script

[#1]

I want to load data in my script but for only the last 7 days. Here is a section from my script where the central fact table is loaded via a qvd file but fails with the new where clause.

-----------------------

TrafficDetails:

LOAD DATATRAFFICDETAILID,

CARRIERID,

RATEPLANID,

CARRIERGROUPID,

RECORDOPENTIME,

RECORDOPENDATE,

IMSI,

SGSNADDR,

ROUNDEDUSAGE,

CYCLEID,

CDRID,

if((ROUNDEDUSAGE = 0 OR IsNull(ROUNDEDUSAGE)), 'Y', 'N') as ZEROBYTE,

'D' AS BILLINGDATATYPE,

CAUSERECORDCLOSING,

RECORDSEQNUMBER,

DURATION,

GGSNADDR,

BYTESUPLINK,

BYTESDOWNLINK

FROM DataTrafficDetails.qvd (qvd)

where RECORDOPENDATE >= (GetDate() - 7);

-------------------------------------------------

What is the correct syntax?

Thanks!

Sandy

[biester]

Hi,

try with "today() -7" instead of GetDate.

Rgds,
Joachim

[#3]

Thanks Joachim, but I am still having issues. Can you advise the full Syntax of the where clause line?

where RECORDOPENDATE >= (GetDate() - 7);

[biester]

Hi,

I was shooting a little to fast.

So the where clause should be:

where (today() - RECORDOPENDATE) <=7

Just to explain: Assuming that RECORDOPENDATE can be interpreted as a valid calendar date, this comparison should calculate the difference of the system date and the recordopendate (in days). Examples:

2009.08.26 <today> - 2009.08.24 <RECORDOPENDATE>: 2 days (<=7 TRUE, so record should be loaded)
2009.08.26 <today> - 2009.08.14 <RECORDOPENDATE>: 12 days (<=7 FALSE, so record should be loaded)

If that doesn't work, might be you have to convert the RECORDOPENDATE with the date#-function; so if RECORDOPENDATE is e. g. '2009.08.24', you should use:

where (today() - date#(RECORDOPENDATE,'YYYY.MM.DD') <=7

Hope I made it clear and no mistake now ;-); but that actually should work.

Rgds,
Joachim

[#5 · Accepted solution]

Hi Joachim, again thanks for your responses. I dod get the following to work (with a little help from Qliktech support 🙂 ).

where num(date((RECORDOPENDATE) > (num(today() -7))));

Cheers

Sandy