topic Re: Limiting data in a LOAD-statement in QlikView

Limiting data in a LOAD-statement

Mon, 27 May 2013 14:41:55 GMT

Hello!

When I select data from an ODBC-source, I can limit the selected data with the WHERE-clause.

I.e.:

SELECT *

from BI.example

where DATE > '01.01.2006';

I'd like to know the equivalent, if I LOAD data from a .qvd-file and only want to load data which match the given criteria, i.e. only one specific customer-ID. The WHERE-clause doesn't work in combination with LOAD, afaik.

I bet this is a simple thing but I didn't figure it out yet.

Re: Limiting data in a LOAD-statement

Mon, 27 May 2013 14:57:03 GMT

This is what I do:

SHE:

LOAD ARTIKELNR,

LABELNR

FROM Struktur\ARTIKEL.qvd

where LABELNR = 33271;

The from-clause alone works this way.

Re: Limiting data in a LOAD-statement

Mon, 27 May 2013 15:06:59 GMT

I forgot to mention the error message. QV recognizes the where-statement as part of the filename and -path.

Re: Limiting data in a LOAD-statement

Mon, 27 May 2013 15:17:05 GMT

use:

LOAD ARTIKELNR, LABELNR FROM Struktur\ARTIKEL.qvd(qvd) where LABELNR = 33271;

make sure the field LABRLNR is number.

if it is string u need to use LABELNR='33271'

Re: Limiting data in a LOAD-statement

Mon, 27 May 2013 15:21:23 GMT

I tried both before. As I said: problem seems to be that QV does interpret the where-statement as part of the filepath (see the error message screenshot).

I do not use a directory-statement to ensure that QV uses relative paths instead of absolutes. This works everywhere in the script but it seems as if it does not in this special case.

Re: Limiting data in a LOAD-statement

Mon, 27 May 2013 15:24:40 GMT

i understand English only.....so cant understand ur error mseg....

may be u can translate

Re: Limiting data in a LOAD-statement

Mon, 27 May 2013 15:28:57 GMT

Error opening file 'D:\Workarea\QVDs\Struktur\ARTIKEL.qvd where LABELNR = 33273' etc.

You can see at the quote signs that QV thinks of the whole expression as filename...

Re: Limiting data in a LOAD-statement

Mon, 27 May 2013 15:31:35 GMT

have you tried (qvd) after .qvd ?

Re: Limiting data in a LOAD-statement

Mon, 27 May 2013 15:34:02 GMT

have u used : FROM Struktur\ARTIKEL.qvd(qvd)

if yes......and it didn't work.....just delete ur script and use the 'Table files' button to select from ur qvd after checking the box 'Relative paths' and just remove the semi colon after the statememt generated and put the where clause.

Re: Limiting data in a LOAD-statement

dmohanty — Mon, 27 May 2013 15:40:20 GMT

Don't forget to add (qvd) after .qvd...Ex: FROM Struktur\ARTIKEL.qvd(qvd)

Or else instead of writing the script, load it from Table Files.

Re: Limiting data in a LOAD-statement

Tue, 28 May 2013 10:14:54 GMT

Thanks a lot, guys. The (qvd) hint was it.

Unfortunately I can't mark all your usefull tips. Sorry for that.

Re: Limiting data in a LOAD-statement

Tue, 28 May 2013 10:22:04 GMT

see my first post.....where i mentioned qvd(qvd)....

and u replied; "I tried both before."

pls read the answer carefully before saying that u've tried....it wastes time and creates confusion....

Re: Limiting data in a LOAD-statement

Tue, 28 May 2013 11:22:31 GMT

Sorry, my answer was correct, but not precise enough.

if it is string u need to use LABELNR='33271'

What I tried was both, with and without quotation marks.