topic how to join two tables from different servers? in QlikView

how to join two tables from different servers?

irsha — Sat, 16 Nov 2024 00:25:26 GMT

Dear all

I have a table in a server and another one in another server i want to join these two when i write the query in sql server every thing is ok but in qlick script i get error:

Table1_label:

[connection to server1]

sql

Select * from Table1

Table2_label:

[connection to server2]

sql

Select * from Table2 inne join Table1_label

please let me know how can i do this

thanks in advance

Re: how to join two tables from different servers?

Or — Thu, 25 Jun 2020 06:10:10 GMT

Load Field from Table1;

Join

Load Field from Table2;

Joins will be done on any fields with identical names.

Re: how to join two tables from different servers?

Vegar — Thu, 25 Jun 2020 06:35:07 GMT

As you are not performing the join in the database you will have to do the join in the QlikView LOAD and not in the SQL SELECT. QlikView performs the join based on field names. The join will be done on all field names that are common between the two Tables.

[connection to server1]
Table1_label:
sql Select * from Table1

Disconnect;

[connection to server2]
Table2_label:
JOIN LOAD * ;
sql Select * from Table2 ;

Disconnect;

Re: how to join two tables from different servers?

irsha — Sat, 27 Jun 2020 09:16:31 GMT

Thanks for reply

i could not solve my problem.

here is my complete code. please let me know how can i correct it

thanks so much

[conncet to server1]

Rate:
SQL
With cte1 as
(SELECT c1 as field1 , c5 ,C9,ROW_NUMBER() OVER(partition by c1 order by c11 DESC) rw
FROM Rate)
SELECT field1, c5 ,C9 FROM cte1 WHERE rw=1;

[connect to server2]
Entity1:
SQL
SELECT E_Field312 as field1,E_Field403,E_Field4526,E_Field4527,E_Field313 FROM Entity1;

[conncet to serever 3]
ENTITY7:
SQL
SELECT [E_Field741],[E_Field207] as field1,[E_Field3075]
FROM Entity7;

connect to server4
left join load *
resident Rate;
left join load *
resident Entity1;
left join load *
resident ENTITY7;
Table:
sql

WITH CTE AS(
SELECT
c9
, field1

,Entity1.[E_Field4527] as field2,
Entity1.[E_Field313] AS field3,
PARSENAME(field1,3) AS field4,
g.GROOH ,
Rate.c5

,ROW_NUMBER() OVER(PARTITION BY field2, field3, field1

ORDER BY field1) RW
FROM EntityD
LEFT join [dbo].[Customer] on EntityD .CustID=[Customer].cust_ID
LEFT JOIN Dep_Type_Groups g
ON g.code=PARSENAME(field1,3)
LEFT JOIN TimeDetail T
ON T.TP_ID=E_Field362
LEFT JOIN TimeDetail T2
ON T2.TP_ID=E_Field361
LEFT JOIN TimeDetail T3
ON T3.TP_ID=E_Field360

where EfectiveDate='1399/03/31'
AND CONVERT(DATE,ENTITY7.E_Field3075)='2020-06-20'
)
select

*
FROM CTE WHERE [RW]=1 ;

Re: how to join two tables from different servers?

Brett_Bleess — Thu, 09 Jul 2020 12:38:49 GMT

So one key piece missing here is the Script/Document log showing what the error is you are receiving, without that, it is difficult to determine where the issue actually is located, at least for me, so here is an article on how to activate that if you do not know, and if you can then attach the log, that may allow someone to provide further information for you.

https://support.qlik.com/articles/000002668

Here is a Design Blog post that may be of some use too:

https://community.qlik.com/t5/Qlik-Design-Blog/To-Join-or-not-to-Join/ba-p/1463102

Regards,
Brett