topic Data from more databases (connect to databases or datawarhouse) in QlikView

Data from more databases (connect to databases or datawarhouse)

francisvandergr — Wed, 13 Oct 2010 23:13:41 GMT

I have a question. We have a qlikview customer who has more databases and the next years they will grow fast. I want now make a good decision before build in qlikview. The reports we gonna make in qlikview are mostly data combined from more databases. For example we have the following databases :

001 Netherlands, 002 Belgium, 003 Germany, 004 United States. For the reports we sometimes want data from 1 administration, and often we want data combined from all administrations or some administrations.

CONNECT

[Provider=SQLNCLI10.1;Integrated Security=SSPI;Persist Security Info=False;User ID="";Initial Catalog=002;Data Source=NB-FRANCIS;Use Procedure for Prepare=1;Auto Translate=True;Packet Size=4096;Workstation ID=NB-FRANCIS;Initial File Name="";Use Encryption for Data=False;Tag with column collation when possible=False;MARS Connection=False;DataTypeCompatibility=0;Trust Server Certificate=False];

Of course i can get all tables from database 002 now (initial catalog). I can get the tables:

Customers,Countrys, Orders, etc.

But when i want this for all administrations. So i can repat the script for all administrations.

Is there an easier way to do this for example to build a datawarehouse with data from all administrations.

Data from more databases (connect to databases or datawarhouse)

pover — Thu, 14 Oct 2010 05:00:55 GMT

I suppose it depends on the ODBC, but the initial catalog is usually optional and you can always refer to a different database directly in the SQL statement. You only have create a new connection string to connect to a different server and you can concatenate similar tables from different servers without any problem in the same script.

Regards.

Data from more databases (connect to databases or datawarhouse)

francisvandergr — Fri, 15 Oct 2010 09:21:08 GMT

Hi Karl,

I have the solution : We build in a loop. Thanx for your time!

FOR

EACH admin IN

$(Administrations)

Orders:

LOAD

Boekjr as Orders\Boekjaar

periode

as Orders\Periode

debnr

as Relatie\ID

// "cmp_name" as Verkooporder/Naam,

// ClassificationId as Verkooporder/ClassificationID

// "cmp_fctry" as,

artcode as Artikelen\Artikelcode

Description

as Orders\ArtikelOmschrijving

Code

as Orders\ProductgroepCode

"u_productgr"

as Orders\ProductgroepOmschrijving

omzet

as Orders\Omzet

aantal

as Orders\Aantal

marge

as Orders\Marge

"vvp_s"

as Orders\VVP

admin

as Orders\Administratie

naam

as Orders\RelatieNaam

PurchaseCurrency

as Orders\Valuta

ordernr

as Orders\Ordernr

faknr

as Orders\FactuurNummer

"orginele regel kostpr"

as Orders\Originele_Regel_Kostprijs

costprice

as Orders\Kostprijs

"Huidig jaar"

as Orders\HuidigJaar

fakdat

Orders\Factuurdatum;

// artcode from 1 for 1 as BudgetUnit;

SQL

SELECT

FROM

[$(admin)]

.dbo.VwhvOmzAlg;