Combine Three table from three databases

c_woehrmann — Tue, 27 Mar 2018 13:11:47 GMT

Hello everyone,

i searched in the forum but i didn't find anything to solve my problem.

First of all i have three different data sources let's named them Source 1, Source 2 and Source 3.

Source 1 and Source 3 are databases from an ERP-System (the same ERP-System) and Source 2 is a Excel-Sheet.

Next the Source 1 has different tables (names of the table are the same in Source 1 and Source 3 but in following i name Customer in A and B to point out that they are different also the same but different are ZNR. and YNr.).

The table 1 arise from a header and a footer table with the composit key number (ANr.) and Typ.

Also table 3 arises in the same way with header and footer table.

A important aspect is that the source 2 (Exel-Sheet) has only a couple of links (numbers) in the table.

Not all numbers from table 1 and table 3 are in the table 2.

In the following a detail of the tables.

Table 1

ZNr. ANr. TYP Price

10 1 A 100,00 €

20 2 A 200,00 €

30 50 B 100,00 €

Table 2

HNr. ANr. BNr.

1 1

1 3

20 2

Table 3

YNr. BNr. Typ Price

10 1 A 100,00 €

20 3 C 200,00 €

40 2 B 300,00 €

So now its time to explain.

ZNr. is a receipt number (from data source 1)

YNr. is a receipt number (from data source 3)

ANr. is a customer number (from data source 1)

BNr. is a customer number (from data source 3)

HNr. is a head number for ANr. and BNr (represents one costumer with different customer numbers).

ANr and Typ build a key as well as BNr and Typ.

Price is the price.

So the idea was to link the table 1 and 3 to one table.

The Following table is a detail what we want to build with Qlik Sense.

HNr. ANr. BNr. ZNr. YNr. Typ Price

1 1 100 A 100,00 €

1 3 20 C 200,00 €

20 2 40 B 300,00 €

50 30 B 100,00 €

I tried to concatenate the table 1 and table 2 and it worked (I thougth).

If i tried to join source 3 it only screens in the diagramm the prices from source 3.

I tried many things but don't get the rigth form.

I tried for example:

Load ZNr.,

ANr. as %KeyCostumer,

typ,

price

from table 1

Concatenate (table 1)

Load YNr.,

BNr. as %KeyCostumer,

typ,

price

from table 2

I my prospects the problem is that if I arises the table 3 the join (or concatenate) are getting the wrong position.

In SQL i can say something like this:

SELECT ANr, BNr.,Price

FROM table1, table 3

INNER JOIN Customers ON ANr.=BNr.;

But in Qlik Sense the join statement is without ON...

I apologize in advance for my english communication skills

I hope somone can help me/us. Thanks a lot for reading...

topic Combine Three table from three databases in Management & Governance

Combine Three table from three databases