Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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!
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.
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.