Qlikview 11 for developers says -
The Origin and Destination Airports dimension tables perform a direct query to the source database. Therefore, the Exists() function cannot be used as described here. A QlikView function might not be interpreted as expected in a direct database query. Therefore, we need to use the Left Keep prefix approach in those two cases to achieve the expected result.
So, I thought why not try to see this in action ! Here is my attempt. Let me know if I got it right.
My SQL "table", a list of people I know -
FirstName LastName
Ken Kwok
Ken Meyer
Ken Myer
Ken Sánchez
My Qlikview Script to load only my friends -
[MyFriends]:
LOAD * INLINE [
FName, LName
Ken,Myer
Ken,Sánchez
];
//List of people I know
OLEDB CONNECT32 TO [long connection string] (XPassword is secret);
LOAD FirstName, LastName;
SQL SELECT DISTINCT FirstName, LastName
FROM AdventureWorks2008R2.Person.Person
WHERE FirstName = 'Ken' // there was a ; after this line. Its removed to add Exists() of qlikview in next line
Where Exists([FirstName], [FName])
AND Exists([LastName], [LName]);
Error on reloading script -
ErrorSource: Microsoft OLE DB Provider for SQL Server, ErrorMsg: Incorrect syntax near the keyword 'Where'.
SQL SELECT DISTINCT FirstName, LastName
FROM AdventureWorks2008R2.Person.Person
WHERE FirstName = 'Ken'
Where Exists([FirstName], [FName])
AND Exists([LastName], [LName])
Is this the reason why you cannot use qlikview's EXISTS() function to compare a field taken from a database to any other field in the script ?
