0 Replies Latest reply: Mar 27, 2018 9:11 AM by Christian Wöhrmann RSS

    Combine Three table from three databases

    Christian Wöhrmann

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