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?

[swuehl]

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

[sunny_talwar]

or may be this:

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

SELECT ....

FROM ...

WHERE ORDDATE > '$(vThreshold)';

UPDATE: Missed one M before

[#4]

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?

[sunny_talwar]

I think you missed the parenthesis

Where ORDDATE > '$(vThreshold)';

[sunny_talwar]

And may be this format for date:

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

[#7]

Worked and got it.  Thanks

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

[sunny_talwar]

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

[#9]

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. 

[swuehl]

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.