Where clause to filter multiple QVDs that inner join

[#1]

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!

[#2]

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.

[jagan]

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.