Qlik Community

Qlik Sense Deployment & Management

Discussion board where members learn more about Qlik Sense Installation, Deployment and Management.

c_woehrmann
New Contributor

Combine Three table from three databases

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...

Community Browser