Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Not applicable

Selected Data Pull

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?

11 Replies
MVP
MVP

Re: Selected Data Pull

I assume the WHERE clause is part of your SQL. Probably your SQL server don't understand Today(), but you can use a variable:

Let vThreshold = Num(Today()-5);

SELECT ....

FROM ...

WHERE ORDDATE > $(vThreshold);

MVP
MVP

Re: Selected Data Pull

or may be this:

LET vThreshold = Date(Today()-5, 'DD-MMM-YYYY');

SELECT ....

FROM ...

WHERE ORDDATE > '$(vThreshold)';

UPDATE: Missed one M before

Not applicable

Re: Selected Data Pull

When I am getting the following:

The following error occurred:

ErrorSource: Microsoft OLE DB Provider for SQL Server, ErrorMsg: Error converting data type varchar to numeric.

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

LIB CONNECT TO 'svtrucosql03';

LOAD ORDUNIQ,
     Date(Date#(ORDDATE, 'YYYYMMDD'), 'MM/DD/YYYY') as ORDDATE,
//      ORDDATE,
     SHIPDATE,
     INVDATE,
     INVWEIGHT;
SQL SELECT ORDUNIQ,
     ORDDATE,
     SHIPDATE,
     INVDATE,
     INVWEIGHT
FROM TRUDAT.dbo.OEORDH Where ORDDATE > '$vThreshold';

Suggestions?

MVP
MVP

Re: Selected Data Pull

I think you missed the parenthesis


Where ORDDATE > '$(vThreshold)';

MVP
MVP

Re: Selected Data Pull

And may be this format for date:


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

Not applicable

Re: Selected Data Pull

Worked and got it.  Thanks

Do I need to do something so you get points for being so helpful?

MVP
MVP

Re: Selected Data Pull

Awesome -

Not just because it gives me point, but because this might help someone else in the future if they have a similar issue and might find it easy to browse this discussion if you close the thread by marking correct and helpful responses.

Qlik Community Tip: Marking Replies as Correct or Helpful

Best,

Sunny

Not applicable

Re: Selected Data Pull

Why do you think it didn't pull just a subset of my data?  It appears as though the whole data set was pulled even though the where clause should have only take the portion within the 5 days. 

MVP
MVP

Re: Selected Data Pull

Probably because you are comparing apple to oranges in the WHERE clause.

You would need to find a format for the variable that the DBMS can compare to the ORDDATE values in your DB table. Maybe just ask your local DB guru how this value needs to look like.