Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
QlikView Fans! We’d love to hear from you.
Share your QlikView feedback with the product team… Click here to participate in our 5-minute survey.
Rules, plus terms and conditions, can be found here.
asmithbi
Contributor II

QVW Load Statement Where Clause

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. 

Screen Shot 2014-02-10 at 1.39.33 PM.png

Data Model 2.png

4 Replies
Not applicable

Re: QVW Load Statement Where Clause

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

AJ

asmithbi
Contributor II

Re: QVW Load Statement Where Clause

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,

Screen Shot 2014-02-10 at 6.36.46 PM.png

Screen Shot 2014-02-10 at 6.40.04 PM.png

Not applicable

Re: QVW Load Statement Where Clause

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

asmithbi
Contributor II

Re: QVW Load Statement Where Clause

Thank you Dathu. 

Community Browser