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

Where clause to filter multiple QVDs that inner join

Hello everyone,

I am getting frustrated with a recurring error that I am trying to resolve.  Basically, I have about 6 QVDs that I am inner joining but want to filter (with where clauses) a combination of criteria across the QVDs.  I get a "field not found" error because I believe Qlikview is assuming I mean to put where restrictions on only the last QVD.  Here an example of what I wrote:

TABLE1:

LOAD

       Number,

       Quote,

       ID

FROM [QVD A];

Inner join

LOAD

      ID,

      Name as Cat_Name,

      Sale

FROM [QVD B];

Inner join

LOAD

      Sale,

      Approvals,

      Primary

FROM [QVD C];

Inner join

LOAD

      Approvals,

      User

FROM [QVD D]

      Where (Cat_Name = 'Example'

      OR

      Number like '6657*')

       AND Primary = 'true';

Upon doing this, I would get a Error: "Field not found: <Cat_Name>".  I believe this is because qlikview is assuming my where clause only meant for the last QVD that I'm loading ([QVD D]).

Does anyone have suggestions on how to properly write this to where I can filter across fields from multiple QvDs that I am loading and joining?  I am thinking about this in a SQL script mindset (for better or worse) in which I define the table fields, define the FROM (and the joins) and then write the where clauses after all the joins are written.  Help is appreciated!

Tags (3)
2 Replies
Not applicable

Re: Where clause to filter multiple QVDs that inner join

Hi,

Your script have some syntax errors. I noticed that you followed a SQL type syntax but QV script is a little bit different. You need to add conditions on the qvd when its loaded not at the end. If there are any conditions with more than one table then you load all and filter with a resident load.

You can try the below script:

TABLE1:

LOAD Number,

     Quote,

     ID

FROM [QVD A];

Inner join

LOAD ID,

     Name as Cat_Name,

     Sale

FROM [QVD B];

Inner join

LOAD Sale,

     Approvals,

     Primary

FROM [QVD C];

Inner join

LOAD Approvals,

     User

FROM [QVD D];

NewTable:

LOAD Number,

     Quote,

     ID,

     Name as Cat_Name,

     Sale,

     Approvals,

     Primary,

     User

Resident Table1

Where (Name = 'Example' OR Number like '6657*') and Primary = 'true';

Drop Table Table1;

Cheers,

Kiran Rokkam.

MVP
MVP

Re: Where clause to filter multiple QVDs that inner join

Hi,

Try this script

Temp:

LOAD

       Number,

       Quote,

       ID

FROM [QVD A];

Inner join

LOAD

      ID,

      Name as Cat_Name,

      Sale

FROM [QVD B];

Table1:

LOAD

      Sale,

      Approvals,

      Primary

FROM [QVD C]

WHERE Primary='true;

INNER JOIN

LOAD

       Number,

       Quote,

       ID,

       Cat_Name,

       Sale

RESIDENT TABLE1

Where (Cat_Name = 'Example'

      OR

      Number like '6657*');

Inner join

LOAD

      Approvals,

      User

FROM [QVD D];

DROP TABLE Temp;

Hope this helps you.

Regards,

Jagan.

Community Browser