topic Re: QVW Load Statement Where Clause in QlikView

QVW Load Statement Where Clause

asmithids — Mon, 10 Feb 2014 21:44:52 GMT

Hello,

I have four separate taps that each contain a separate load statement (see first screen shot). I am trying to add a WHERE clause on the JrnlHdr load statement (i.e. WHERE JrnlHdr.JrnlKey_Journal IN (3,5,11) and have it apply to all of the other load statements (see data model screen shot). When I add the WHERE clause to the "JrnlHdr" load statement, the other load statements do not reduce the number of fetched records as expected.

Thank you in advance for any assistance.

Re: QVW Load Statement Where Clause

Mon, 10 Feb 2014 22:24:08 GMT

Hey Alec,

You have to use 'Where Exists' statement.

Load JrnlHdr table first and use your filters. So that table is now filtered
Then load the remaining three tables with Where exists and JrnlRow should be the second table to be loaded in your script.

eg; Load * From JrnlRow

Where Exists(PostOrder);

Load * From Products

Where Exists(ItemRecordNumber);

etc.

That will reduce the data with respect to the very first filter.

Hope it helps

Thanks

Re: QVW Load Statement Where Clause

asmithids — Mon, 10 Feb 2014 23:43:10 GMT

Thank you AJ!

I added the Where Exists to the JrnlRow load statement (see screen shot). However, when I run the reload, I get the second screen shot error.

Thanks again,

Re: QVW Load Statement Where Clause

Tue, 11 Feb 2014 00:01:39 GMT

Hi Smith, you can't use the Qlikview functions in the SQL statements in the script. I believe, you are using the AJ suggested method on SQL statements and so you got the error. So please use Match function in the precedent Load like below:

Customers:

LOAD * Where Match(JmlHdr,3,5,11);

SQL SELECT field1,

field2,

field3,

field4

...

From Customers;

or you can use SQL IN function in the SQL statement like below:

Customers:

SQL SELECT field1,

field2,

field3,

field4

...

From Customers Where JmlHdr IN (3,5,11);

You can use either of method

Re: QVW Load Statement Where Clause

asmithids — Tue, 11 Feb 2014 00:50:38 GMT

Thank you Dathu.