topic Re: Concatenating 2 tables in 2 different databases in QlikView

Concatenating 2 tables in 2 different databases

Wed, 25 Nov 2020 16:16:04 GMT

Hi All,

I am trying to concatenate two tables (table1 and table4) from 2 different databases in QV script as below (table1 and table4 are joined with different tables within the same database). The script works till the highlighted lines but does give an error when the rest is run all together. Error message is as below:

ERROR MESSAGE

Table not found

INNER JOIN (Table 4)

Table 5:

First 10 LOAD Company,

Field 1,2,3 etc.

Could you please let me know if it is possible to join table4 in the second database first and then concatenate it to table1 created in the first database?

Thanks in advance for all the support.

QV SCRIPT

OLEDB CONNECT TO database

Table 1:
First 10 LOAD

Field 1,2,3 etc
SQL SELECT *
from database 1

INNER Join (Table 1)
Table 2:
First 10 LOAD

Field 1,2,3 etc.

SQL SELECT *
FROM database 1

INNER JOIN (Table 1)
Table 3:
First 10 LOAD

Field 1,2,3 etc.

FROM database 1

Table 4:
Concatenate First 10 LOAD
Field 1,2,3 etc.

SQL SELECT *
FROM database 2

INNER JOIN (Table 4)
Table 5:
First 10 LOAD

Field 1,2,3 etc.

SQL SELECT *
FROM database 2

INNER JOIN (Table 4)
Table 6:
First 10 LOAD
Field 1,2,3 etc.

SQL SELECT *
FROM database 2

INNER JOIN (Table 4)
Table 7:
First 10 LOAD

Field 1,2,3 etc.

SQL SELECT *
FROM database 2

Re: Concatenating 2 tables in 2 different databases

vishsaggi — Tue, 14 Feb 2017 21:46:21 GMT

May be try this?

OLEDB CONNECT TO database

Table 1:
First 10 LOAD

Field 1,2,3 etc
SQL SELECT *
from database 1

INNER Join (Table 1)
Table 2:
First 10 LOAD

Field 1,2,3 etc.

SQL SELECT *
FROM database 1

INNER JOIN (Table 1)
Table 3:
First 10 LOAD

Field 1,2,3 etc.

FROM database 1

NoConcatenate
Table 4:
First 10 LOAD
Field 1,2,3 etc.

SQL SELECT *
FROM database 2

INNER JOIN (Table 4)
Table 5:
First 10 LOAD

Field 1,2,3 etc.

SQL SELECT *
FROM database 2

INNER JOIN (Table 4)
Table 6:
First 10 LOAD
Field 1,2,3 etc.

SQL SELECT *
FROM database 2

INNER JOIN (Table 4)
Table 7:
First 10 LOAD

Field 1,2,3 etc.

SQL SELECT *
FROM database 2

Re: Concatenating 2 tables in 2 different databases

maxgro — Tue, 14 Feb 2017 22:26:16 GMT

If I understand

- create table 1 and join 2 and 3 with 1

- create (noconcatenate) table 4 and join 5 and 6 and 7 to 4

- add table 4 to table 1

- drop table 4

[Table 1]:
First 10 LOAD

Field 1,2,3 etc
SQL SELECT *
from database 1

INNER Join ([Table 1])
//Table 2:
First 10 LOAD

Field 1,2,3 etc.

SQL SELECT *
FROM database 1

INNER JOIN ([Table 1])
//Table 3:
First 10 LOAD

Field 1,2,3 etc.

FROM database 1

end of table 1

start table 4

[Table 4]:
NoConcatenate

First 10 LOAD
Field 1,2,3 etc.

SQL SELECT *
FROM database 2

INNER JOIN [Table 4]
//Table 5:
First 10 LOAD

Field 1,2,3 etc.

SQL SELECT *
FROM database 2

INNER JOIN [Table 4]
//Table 6:
First 10 LOAD
Field 1,2,3 etc.

SQL SELECT *
FROM database 2

INNER JOIN [Table 4]
//Table 7:
First 10 LOAD

Field 1,2,3 etc.

SQL SELECT *
FROM database 2

Concatenate ([Table 1])

load * resident [Table 4];

drop table [Table 4];

Re: Concatenating 2 tables in 2 different databases

Wed, 15 Feb 2017 12:12:17 GMT

Many thanks both of you.

Massimo, that is exactly what I wanted to do and it worked. Thank you very much.

One more request is that if you could please explain the logic of what the last 3 lines you have added do and if dropping table4 means that if I am loosing any fields that belongs to this table or any disadvantages of dropping it? Also would the same work if I swap table 1 and 4 in the last 3 lines you added?

This will help me understand resident load and drop table functions in my search.

Again many thanks for all the support.

Re: Concatenating 2 tables in 2 different databases

maxgro — Wed, 15 Feb 2017 19:04:20 GMT

One more request is that if you could please explain the logic of what the last 3 lines you have added do and if dropping table4 means that if I am loosing any fields that belongs to this table or any disadvantages of dropping it?

Before the last 3 lines you have 2 tables 1 and 4.

As you want 1 table, use concatenate to add Table 4 to Table 1.

After that you have Table 1 with Table 1 and Table 4 data

and Table 4; so, remove (drop) Table 4

Also would the same work if I swap table 1 and 4 in the last 3 lines you added?

Yes

Concatenate ([Table 1])

load * resident [Table 4];

drop table [Table 4];

Re: Concatenating 2 tables in 2 different databases

Thu, 16 Feb 2017 10:05:14 GMT

Many thanks Massimo.