topic Adding WHERE clause in QlikView

Adding WHERE clause

Thu, 01 Oct 2009 02:19:04 GMT

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

Adding WHERE clause

Thu, 01 Oct 2009 02:37:24 GMT

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

Adding WHERE clause

biester — Thu, 01 Oct 2009 02:37:25 GMT

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

the database (data source) documentation and
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

Adding WHERE clause

johnw — Thu, 01 Oct 2009 02:38:12 GMT

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.

Adding WHERE clause

biester — Thu, 01 Oct 2009 02:50:44 GMT

.. 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

Adding WHERE clause

Thu, 01 Oct 2009 04:18:57 GMT

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

Adding WHERE clause

johnw — Thu, 01 Oct 2009 04:30:09 GMT

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.

Adding WHERE clause

Thu, 01 Oct 2009 18:52:23 GMT

I got it, Thanks

For any SAP B1 users.

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

Adding WHERE clause

Fri, 02 Oct 2009 00:38:23 GMT

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.

Adding WHERE clause

biester — Fri, 02 Oct 2009 00:42:22 GMT

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