topic How to Join two diffrent source datbase tables in QlikView

How to Join two diffrent source datbase tables

vijaysahu2 — Wed, 25 Nov 2020 16:16:04 GMT

Hi All

We have view created in SQL Server and two SQL tables are used in that, but because of my migration project

Underlying table are distributed in SQL and Oracle .

Below is the tables and join used in SQL server - QV and Master tables

SELECT UPPER(Name) AS 'LINK', [Exec], DisplayName, 'Div' AS 'PermissionRule'

FROM QV Q

JOIN ( SELECT DISTINCT 'JB' AS 'Entity', [Name] AS 'Exec'

FROM Master

WHERE Division = 'JB' ) T ON 'JB' = T.Entity

WHERE QVAppID = 3 AND AllJEBData = 1

Because of this Migration QV table is left in SQL and Master table is coming from Oracle. Now I have build above query in Qlikview, for this I have fetched data from SQL and Oracle in separate Qlikview Tables but can not build this join

Please suugest

Re: How to Join two diffrent source datbase tables

wandererroch — Fri, 03 May 2019 16:41:10 GMT

In Qlikview you would have to load them separately and join the resulting tables.

I've also made things like this work using linked servers and ODBC connections and doing the query on either the oracle or sql server directly.

Re: How to Join two diffrent source datbase tables

vijaysahu2 — Fri, 03 May 2019 16:52:36 GMT

Linked server is not approved in my project and this logic needs to be build in Qlikview only. I tried something like below but not sure if it will give the same result or not.

$(Include=..\inputdata\QlikviewDBCon.txt);

call SQLConnnection;

QV:
Load *;
SQL SELECT UPPER(Name) AS 'LINK', DisplayName, 'Div' AS 'PermissionRule'
FROM QV Q WHERE QVAppID = 3 AND AllJEBData = 1;

$(Include=..\inputdata\salescon.txt);

call OracleConnection;

Inner JOIN(QV)

JEB:
Load *;
SQL SELECT
distinct 'JB' AS "Entity", name AS "Exec"

FROM
[master]

WHERE Division = 'JB';