8 Replies Latest reply: Dec 4, 2013 8:52 PM by Jean-Pierre Bakhache RSS

    linking 2 tables

    Marco Rizzo

      Hi everybody.

       

      I've got a simple question (but difficult for me).

       

      I've got this 2 sql tables.

       

      tab1:

      Code
      Product
      1cap
      2jeans
      3coat
      4other

       

      tab2:

      Customer
      Product1Product2Product3
      mark1null3
      paul2nullnull
      jenny142

       

      How can I link them, so that I can obtain (in qvw document) this final table ?

      Header 1Header 2Header 3Header 4
      markcapnullcoat
      pauljeansnullnull
      jennycapotherjeans

       

      Thank you so much

       

      Marco

        • Re: linking 2 tables
          Sokkorn Cheav

          Hi Marco,

           

          My idea is using ApplyMap() function. Something like this:

           

          [Product]:

          Mapping

          Load

               Code,

               Product;

          Select Code, Product From ProductTable;

           

          [Customer]:

          Load

               Customer,

               ApplyMap('Product',Product1,Null())     AS [Product1],

               ApplyMap('Product',Product2,Null())     AS [Product2],

               ApplyMap('Product',Product3,Null())     AS [Product3];

          Select Customer, Product1, Product2, Product3 From CustomerTable;

           

          You need to change table name accordingly.

           

          Regards,

          Sokkorn

            • Re: linking 2 tables
              Marco Rizzo

              Thank you. I'll try as soon as possible.

               

              And what about a join inside qlikview's script ?

               

              P.S. Sorry for my English

                • Re: linking 2 tables
                  Clever Anjos

                  You could use a join like this, but Sokkorn approach is better

                   

                  [Product]:

                  Load

                       Code,

                       Product;

                  Select Code, Product From ProductTable;

                   

                  [Customer]:

                  Load

                       Customer,

                        [Product1],

                       [Product2],

                        [Product3];

                  Select Customer, Product1, Product2, Product3 From CustomerTable;

                   

                  Left join(Customer)

                  LOAD

                       Code as Product1,

                       Product as ProductDescription1

                  resident Product;

                  Left join(Customer)

                  LOAD

                       Code as Product2,

                       Product as ProductDescription2

                  resident Product;

                  Left join(Customer)

                  LOAD

                       Code as Product3,

                       Product as ProductDescription3

                  resident Product;

                  • Re: linking 2 tables
                    Sokkorn Cheav

                    Hi Marco,

                     

                    If you wish join table, you can try suggestion from Clever Anjos. For me I prefer ApplyMap() because it have better load performance.

                     

                    Regards,

                    Sokkorn

                • Re: linking 2 tables
                  Marco Rizzo

                  Sorry but I have some difficulties. This was my real script :

                   

                  INF01:

                  LOAD *;

                  SQL SELECT   

                   

                      "INF01_CATEGORIA_INF03" AS [Codice Categoria],

                      "INF01_CODGESTIONALE" as [Cod. Gest. Ragione Sociale],

                      "INF01_DITTA" as [Ditta Crm],

                      "INF01_MACRO_INF02" as [Codice Macrocategorie],

                      "INF01_PRODOTTO1_INF04" AS [Codice Prodotti1],   

                      "INF01_PRODOTTO2_INF04" AS [Codice Prodotti2],

                      "INF01_PRODOTTO3_INF04" AS [Codice Prodotti3],

                      "INF01_PRODOTTO4_INF04" AS [Codice Prodotti4],

                      "INF01_PRODOTTO5_INF04" AS [Codice Prodotti5]

                   

                  FROM GRUPPOINFOR.dbo."INF01_CONTATTI";

                   

                  INF04:

                  LOAD *;

                  SQL SELECT

                   

                          "INF04_CODICE" AS [Codice Prodotti],

                   

                          "INF04_DESCRIZIONE" as [Descrizione Prodotti]

                   

                  FROM GRUPPOINFOR.dbo."INF04_PRODOTTI";

                   

                  I want to link

                   

                      "INF01_PRODOTTO1_INF04" AS [Codice Prodotti1],   

                      "INF01_PRODOTTO2_INF04" AS [Codice Prodotti2],

                      "INF01_PRODOTTO3_INF04" AS [Codice Prodotti3],

                      "INF01_PRODOTTO4_INF04" AS [Codice Prodotti4],

                      "INF01_PRODOTTO5_INF04" AS [Codice Prodotti5]

                   

                  with

                   

                  "INF04_CODICE" AS [Codice Prodotti].

                   

                  This is the way I set ApplyMap function. What is wrong ?

                   

                  INF04:

                   

                  Mapping

                   

                  LOAD

                   

                       "INF04_CODICE" AS [Codice Prodotti],

                   

                          "INF04_DESCRIZIONE" as [Descrizione Prodotti];

                   

                  SQL SELECT

                   

                          "INF04_CODICE" AS [Codice Prodotti],

                   

                          "INF04_DESCRIZIONE" as [Descrizione Prodotti]

                   

                  FROM GRUPPOINFOR.dbo."INF04_PRODOTTI";

                   

                  INF01:

                   

                  LOAD

                   

                  "INF01_CATEGORIA_INF03" AS [Codice Categoria],

                   

                      "INF01_CODGESTIONALE" as [Cod. Gest. Ragione Sociale],

                   

                      "INF01_DITTA" as [Ditta Crm],

                   

                      "INF01_MACRO_INF02" as [Codice Macrocategorie],

                   

                      ApplyMap("INF04_CODICE","INF01_PRODOTTO1_INF04", null()) AS [Codice Prodotti1],

                   

                      ApplyMap("INF04_CODICE","INF01_PRODOTTO2_INF04", null()) AS [Codice Prodotti2],

                   

                      ApplyMap("INF04_CODICE","INF01_PRODOTTO3_INF04", null()) AS [Codice Prodotti3],

                   

                      ApplyMap("INF04_CODICE","INF01_PRODOTTO4_INF04", null()) AS [Codice Prodotti4],

                   

                      ApplyMap("INF04_CODICE","INF01_PRODOTTO5_INF04", null()) AS [Codice Prodotti5];

                   

                  SQL SELECT    

                   

                      "INF01_CATEGORIA_INF03" AS [Codice Categoria],

                   

                      "INF01_CODGESTIONALE" as [Cod. Gest. Ragione Sociale],

                   

                      "INF01_DITTA" as [Ditta Crm],

                   

                      "INF01_MACRO_INF02" as [Codice Macrocategorie],

                   

                      "INF01_PRODOTTO1_INF04" AS [Codice Prodotti1],   

                   

                      "INF01_PRODOTTO2_INF04" AS [Codice Prodotti2],

                   

                      "INF01_PRODOTTO3_INF04" AS [Codice Prodotti3],

                   

                      "INF01_PRODOTTO4_INF04" AS [Codice Prodotti4],

                   

                      "INF01_PRODOTTO5_INF04" AS [Codice Prodotti5]

                   

                  FROM GRUPPOINFOR.dbo."INF01_CONTATTI";

                   

                  I've got the message :

                   

                  Field not found- <INF04_CODICE>

                   

                  SQL SELECT

                          "INF04_CODICE" AS [Codice Prodotti],

                          "INF04_DESCRIZIONE" as [Descrizione Prodotti]

                  FROM GRUPPOINFOR.dbo."INF04_PRODOTTI"

                   

                   

                    • Re: linking 2 tables
                      mayilvahanan ramasamy

                      hi

                       

                      you replace "INF04_CODICE" AS [Codice Prodotti];

                       

                      So try like this

                       

                      LOAD

                       

                           [Codice Prodotti],

                       

                              [Descrizione Prodotti];

                       

                      SQL SELECT

                       

                              "INF04_CODICE" AS [Codice Prodotti],

                       

                              "INF04_DESCRIZIONE" as [Descrizione Prodotti]

                       

                      FROM GRUPPOINFOR.dbo."INF04_PRODOTTI";

                    • Re: linking 2 tables
                      Marco Rizzo

                      Hi. Maybe I'm not able to explain well what I need.

                      I give you an easier example.

                       

                      file1 script

                      tab1:

                      load*;

                      SQL SELECT codice as [codice prodotto],

                          prodotto as [descrizione prodotto]

                      FROM GRUPPOINFOR.dbo.Tabella1;

                       

                      tab2:

                      load*;

                      SQL SELECT codicecliente as [codice cliente],

                          codprodotto1 as [codice prodotto 1],

                          codprodotto2 as [codice prodotto 2],

                          codprodotto3 as [codice prodotto 3],

                          codprodotto4 as [codice prodotto 4]

                      FROM GRUPPOINFOR.dbo.Tabella2;

                       

                      The result is image1.

                       

                      file2 script (with ApplyMap function):

                      tab1:

                      Mapping

                      Load

                          [codice prodotto],

                          [descrizione prodotto];

                       

                      SQL SELECT codice as [codice prodotto],

                          prodotto as [descrizione prodotto]

                      FROM GRUPPOINFOR.dbo.Tabella1;

                       

                      tab2:

                      Load

                          [codice cliente],

                          ApplyMap('[codice prodotto]',[codice prodotto 1],null()) as [codice prodotto 1],

                          ApplyMap('[codice prodotto]',[codice prodotto 2],null()) as [codice prodotto 2],

                          ApplyMap('[codice prodotto]',[codice prodotto 3],null()) as [codice prodotto 3],

                          ApplyMap('[codice prodotto]',[codice prodotto 4],null()) as [codice prodotto 4];

                       

                      SQL SELECT codicecliente as [codice cliente],

                          codprodotto1 as [codice prodotto 1],

                          codprodotto2 as [codice prodotto 2],

                          codprodotto3 as [codice prodotto 3],

                          codprodotto4 as [codice prodotto 4]

                      FROM GRUPPOINFOR.dbo.Tabella2;

                       

                      The result is image2. This show only one table (image2a) instead first case (image1a).

                       

                      I'd like to obtain this:

                      codice cliente
                      prodotto 1prodotto 2prodotto 3prodotto 4
                      1evolvegammametodo
                      2gammametodo
                      3evolvepaghe
                      4metodopaghe
                      5paghegamma

                       

                      THANK YOU VERY VERY MUCH ...

                       

                      Marco