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,



      from table 1


      Concatenate (table 1)



      Load YNr.,

      BNr. as %KeyCostumer,



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