Adding WHERE clause

[#1]

Is there a specific place I can find documentation on how to add a WHERE clause.

I have a table OINV with a field DocDate.

I only want records with a DocDate >6/30/2009

[#2]

Extend the LOAD?SELECT with where clause.

Eg.

LOAD

field1,

field2

...

fieldn

FROM

xx.QVD (qvd

)

where

fleild1='AU'

;

Just search the help. U'll get examples.

-Arun

[biester · Accepted solution]

Hi katywall,

welcome to the community.
Now back to crude reality!

(note the rhymes 😉 - sorry, I'm a little infantile)

Well, the specific places are

1. the database (data source) documentation and
2. the Qlik View Reference

I always emphasize these two in this respect because there is always a confusion about LOAD and QlikView functions and SELECT and database functions.

But actually a WHERE should work with LOAD as well as with SELECT, so it would be better to post the code in question.

Rgds,
Joachim

[johnw]

You can bring up the help file and look for "where" on the index. It has information for both LOAD and SQL statements.

However, you may run into a further difficulty that a lot of people have. The two dates being compared in the "where" MUST have the same format, or it won't work properly, even though it may not report an error.

If, for example, your database stores the date as an integer like 20090630, and you put the WHERE clause in your SQL, you would need to say "where DocDate > 20090630". Or maybe you're loading from a QVD that used QlikView's internal date format. In that case, you would need to compare it to a QlikView date, such as "where DocDate > date#(20090630,'YYYYMMDD')". In either case, just saying "where DocDate > 6/30/2009" wouldn't work. Just something to be aware of.

[biester]

.. sorry, I forgot of course ;-))

3. John Witherspoon ...... it's always worth reading his (sometimes extensive but the more informative) answers ... and I love his decent hint "bring up the help file" ...... ;-))

Rgds,
Joachim

[#6]

This is the code that does not work. The data is from SAP B1 if it makes a difference.

when the DocDate is selected it appears as 5/1/2009 12:00:00 AM

ODBC

CONNECT TO

SAP;

SQL

SELECT

CardCode,

CardName,

DocDate,

DocEntry,

DocNum,

"Indicator"

FROM

BIHProduction.dbo.OINV;

Where DocDate > '6/30/2009';

[johnw]

I suspect you don't want the semicolon after BIHProduction.dbo.OINV, but I don't do any SAP, so can't say for sure what that SQL implementation looks like. I also suspect that you're having the date format problem I mentioned. By putting it in quotes, you're specifying a text format for the date. Your database is almost certainly NOT storing the DocDates as text. You need to figure out what format it is using, and use the same format in the comparison.

I would suggest testing the SQL outside of QlikView. I suspect it won't work, and that this therefore isn't a QlikView issue. If I'm right, first get it working outside of QlikView. Only copy it to QlikView when you have it working.

[#8]

I got it, Thanks

For any SAP B1 users.

Where DocDate >={ts'2009-06-30 00:00:01'}

[#9]

Well I thought I had it.

That works in one table but in a different database I need between 2 dates.

Where "invoice_date" >={ts '2007-11-01 00:00:01};

work great but adding

Where "invoice_date" >={ts '2007-11-01 00:00:01}

and "invoice_date" < {ts '2009-06-30 00:00:00};

does not.

Thanks again for any assistance.

[biester]

You didn't enclose the strings properly between apostrophes - at least in the examples you posted here ( e.g. {ts '2007-11-01 00:00:01} ). Perhaps that's the rather unspectacular reason. That's all I can contribute for now.

Rgds,
Joachim