Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi,
I have the following SQL query. This works when I run it in a standard SQL query engine (e.g. SQL Server etc)
But it doesn't work in QlikView. Is there a problem with QlikView when it comes to using multiple joins?
One SQL join works but as soon as I do multiple joins it doesn't.
I have ensured the field names are correct and table names are correct so this is not the issue.
SELECT
OrderID,
Orders.CustomerID,
CustomerName,
Orders.EmployeeID,
FirstName,
LastName,
OrderDate
FROM
Orders
INNER JOIN
[Customers]
ON
Orders.CustomerID = Customers.CustomerID
Inner JOIN
Employees
ON
Orders.EmployeeID = Employees.EmployeeID
really it is not necessary as the two join qlikview automatically relate the tables if the field name is called equal
you can try this.
As Henric says: Don't join - use Applymap instead
Qlik Design Blog : Don't join - use Applymap in... | Qlik Community
QlikView is a different animal to SQL and is designed for analytical BI, whereas SQL is designed for transactional systems. Doing things the QlikView way results in good scripts and good data models. Fighting against QlikView and trying to do things the SQL way results in pain.
There is an old saying about square pegs and round holes.
Such behaviour has nothing to do with qlikview then qlikview didn't perform the sql-statement - qlilkview transferred only the sql-statement per odbc/oledb-driver to the database which then execute the query and returned the result to qlikview again.
In your case might maybe the driver not appropriate or the query take too long and any timeouts happens or your network isn't stable enough. But before I would check if your user has the proper access-rights to your second join-table Employees which will be probably have some restrictions. Sometimes are external queries on certain data forbidden, too.
- Marcus
Thank you for your replies but I still don't understand why this method won't work.
There seems to be a problem with the way QlikView processes multiple join in SQL Select, FROM part of scripting to me.
I know the result is possible to achieve using QlikView code but I want to know why SQL code won't work when it works in other engines.
What kind of "not work" ? Does it fail with an error, or run ok but returns unexpected data ?
Could you share the log file from the load ?
I would say share log file and your script? Do you have preceding LOAD statement after SQL SELECT? I would think to get the same results from your SQL query in QlikView and within any other places.
I tried your query on a sql server database and it works.
I tried the same query in Qlikview, using the same sql database, the script is below and it works, same result; it works.
Could you please post the error?
Qlikview doesn't run the sql query, it just send the query to the DBMS and the DBMS send the result back to Qlik. Every query that runs in sql should run in QlikView.
OLEDB CONNECT32 TO.............;
SELECT
OrderID,
Orders.CustomerID,
CustomerName,
Orders.EmployeeID,
FirstName,
LastName,
OrderDate
FROM Orders
INNER JOIN [Customers] ON Orders.CustomerID = Customers.CustomerID
Inner JOIN Employees ON Orders.EmployeeID = Employees.EmployeeID;