topic Combine data from 2 databases in QlikView

Combine data from 2 databases

francisvandergr — Mon, 20 Sep 2010 09:00:31 GMT

I have a question. I have 2 databases with 3 important tables. The tables in the different databases have the same fields. Example:

Database 1: Table GRBK Fields: Number,Year,Period,Amount

Database 2 Table GRBK Fields: Number,Year,Period,Amount

It is possible that both databases have the same data :

Database 1: Table GRBK 2000,2010,9,100

Database 2: Table GRBK 2000,2010,9,100

In Qlikview i need both records. Anybody knows how to combine both records ?

Combine data from 2 databases

Miguel_Angel_Baeyens — Mon, 20 Sep 2010 09:08:40 GMT

Hello Francis and welcome to the Forums,

Do something like the following:

LOAD *; SQL SELECT * FROM Database1; CONCATENATE LOAD *; SQL SELECT * FROM Database2;

Concatenate keyword is not needed when both tables have the same number of fields and fields are named alike. Anyway, I set it here should you have some differences between both tables.

Hope that helps

Combine data from 2 databases

jonathandienst — Mon, 20 Sep 2010 09:08:53 GMT

You could do something along these lines:


DATATABLE:
SQL SELECT Number,
 Year,
 Period,
 Amount,
 'Database1' AS Source
FROM Database1.GRBK;

CONCATENATE
SQL SELECT Number,
 Year,
 Period,
 Amount,
 'Database2' AS Source
FROM Database2.GRBK;

Jonathan

Combine data from 2 databases

Mon, 20 Sep 2010 09:09:23 GMT

Try something like the following

CONNECTION STRING

Select

Orders_no,
Order_date,
Number,
Year,
Amount,
Period,
'DB1' as MyDatabase,
etc
From db1.scheme.orders_table;

CONCATENATE

CONNECTION STRING

Select

Orders_no,
Order_date,
Number,
Year,
Amount,
Period,
'DB2' as MyDatabase,
etc
From db2.scheme.orders_table;

Combine data from 2 databases

francisvandergr — Mon, 20 Sep 2010 10:09:36 GMT

Thanx to all. Jonathan i use your script. And of course it works. 🙂

Combine data from 2 databases

francisvandergr — Mon, 20 Sep 2010 10:16:50 GMT

Must i close this question on this forum?

Combine data from 2 databases

Miguel_Angel_Baeyens — Mon, 20 Sep 2010 10:41:29 GMT

I'd recommed you to do that, so any other user looking for something similar will find the question as solved.