Selected Data Pull

[#1]

All,

I have had such helpful responses so I'll try another.  I would like to restrict the amount of data pulled from my SQL database to only the 5 days prior to when I load.  One field in my data is ORDDATE so I would like to pull the latest 5 days.

I think I would use something like Where ORDDATE > Today()-5 or such.

Any suggestions?

[sunny_talwar]

I agree with Stefan that the where clause might not have worked because of which it pulled all the data. I shared the date format (DD-MMM-YYYY) based on my experience with Oracle. This seem to have worked with it, but depending on what database is, the where statement might need another kind of format.

If you have run the same query, what format would have used for the Where clause? if you can figure that out then you can format your variable in this way

DD or dd -> for date formatted 01, 02, 03

D or d -> for date formatted 1, 2, 3

MM -> for Month 01, 02, 03 (don't use mm because it is used for minutes in QlikView)

M -> for Month formatted 1, 2, 3

MMM -> for 3 letter month name Jan, Feb (NOTE: This depend on your environmental settings and might give you different result based on what is used in the set statement)

MMMM -> for the complete month name January, February (NOTE: again based on environmental variable)

YY or yy -> for two digit year 15, 16

YYYY or yyyy -> for four digit years 2015,2016

And then different ways to join them using - or / or .

Examples - > DD.MM.YYYY, M.D.YYYY, DD/MMM/YYYY, YY-MM-DD

So once you figure out the format, use that to create your variable and send it to the where statement.

I hope this will help you.

Best,

Sunny

[swuehl]

You can find the format codes definitions e.g. here

Conventions for number and time formats ‒ QlikView‌

On Format Codes for Numbers and Dates

Again, the basic principle is that the SQL part including the WHERE clause is sent to the SQL driver as is and executed by the driver. So you need to create a code that is working with your DBMS.

The only thing here where Qlik plays a role is substituting the Qlik variable to text using dollar sign expansion.

Having said this, assuming you are using MS SQL server, you could also try something like

Let vThreshold = Date(Today()-5,'YYYY-MM-DD');

SQL SELECT ORDUNIQ,

     ORDDATE,

     SHIPDATE,

     INVDATE,

     INVWEIGHT

FROM TRUDAT.dbo.OEORDH WHERE ORDDATE > {d '$(vThreshold)'};

But again, if you are not familiar with your DBMS and you want to use SQL, ask your local SQL developer to help you with the syntax or variable format.