Loading data from a database and Qlikview's Exists()

Mon, 21 Apr 2014 22:32:20 GMT

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 ?

Re: Loading data from a database and Qlikview's Exists()

Mon, 21 Apr 2014 23:47:28 GMT

You cannot use Where Exists with the SQL statement. You can use that only in a load statement.

Thanks,

Ram

Re: Loading data from a database and Qlikview's Exists()

Mon, 21 Apr 2014 23:51:16 GMT

Yes, I wanted to see if could demonstrate why you cannot use qlikview's WHERE EXISTS() with an SQL load.

Re: Loading data from a database and Qlikview's Exists()

Tue, 22 Apr 2014 00:07:20 GMT

then what is your question?

Re: Loading data from a database and Qlikview's Exists()

Tue, 22 Apr 2014 01:02:23 GMT

Question was - did I illustrate the concept correctly

Re: Loading data from a database and Qlikview's Exists()

Tue, 22 Apr 2014 01:10:02 GMT

You can say that. yes.

topic Loading data from a database and Qlikview's Exists() in QlikView

Loading data from a database and Qlikview's Exists()

Re: Loading data from a database and Qlikview's Exists()

Re: Loading data from a database and Qlikview's Exists()

Re: Loading data from a database and Qlikview's Exists()

Re: Loading data from a database and Qlikview's Exists()

Re: Loading data from a database and Qlikview's Exists()