5 Replies Latest reply: Oct 24, 2012 12:59 PM by Stefan Wühl RSS

    How to join two tables

    Sujeet Panda

      Hi,

       

      I have two tables

       

      Country1:

      LOAD Country,

           Capital,

           [Area(km.sq)],

           [Population(mio)],

           [Pop. Growth],

           Currency,

           Inflation,

           [Official name of Country]

      FROM

      [C:\Users\Admin\Desktop\QV\Sujeeth panda\Data Sources\Country1.csv]

      (txt, codepage is 1252, embedded labels, delimiter is ',', msq);

       

      and

       

       

      Country2:

      LOAD Country,

           Capital ,

           [Area(km.sq)] ,

           [Population(mio)] ,

           [Pop. Growth] ,

           Currency ,

           Inflation ,

           [Official name of Country]

      FROM

      [C:\Users\Admin\Desktop\QV\Sujeeth panda\Data Sources\Country2.csv]

      (txt, codepage is 1252, embedded labels, delimiter is ',', msq);

       

       

       

      I want to get the capitals from two tables into a list box. Please let me know how can I create a composite key to solve this issue. Because I learner of Qlikview, I am not aware of the steps to get data from multiple table.

       

      Please help me in getting the solution.

       

      Thanks,

      Sujeet

        • Re: How to join two tables
          Stefan Wühl

          QV will automatically concatenate the records of tables you are reading in, when the number of fields and the field names are the same in both tables. It looks like this is the case in your setting. So when executing the script, I think you are only getting one table in the data model, Country1, but the fields will contain data of both tables.

          Creating a list box of field Capital should show all read in capitals.

           

          Hope this helps,

          Stefan

            • Re: How to join two tables
              Sujeet Panda

              Hi Stefan,

               

              Thanks for answering. But I want to elaborate my problem further.

               

              I have got a requirement like

               

              Country1:

              LOAD Country ,

                   Capital ,

                   [Area(km.sq)] ,

                   [Population(mio)] ,

                   [Pop. Growth],

                   Currency ,

                   Inflation,

                   [Official name of Country]

              FROM

              [C:\Users\Admin\Desktop\QV\Sujeeth panda\Data Sources\Country1.csv]

              (txt, codepage is 1252, embedded labels, delimiter is ',', msq);

               

               

              Country2:

              LOAD Country ,

                   Capital ,

                   [Area(km.sq)] ,

                   [Population(mio)] ,

                   [Pop. Growth] ,

                   Currency ,

                   Inflation ,

                   [Official name of Country]

              FROM

              [C:\Users\Admin\Desktop\QV\Sujeeth panda\Data Sources\Country2.csv]

              (txt, codepage is 1252, embedded labels, delimiter is ',', msq);

               

              Country3:

              LOAD Country ,

                   [Official name of Country] ,

                   [Area(km.sq)] as area3

              FROM

              [C:\Users\Admin\Desktop\QV\Sujeeth panda\Data Sources\Country3.csv]

              (txt, codepage is 1252, embedded labels, delimiter is ',', msq);

               

               

              Once I reload the sricpt I get synthetic table. To resolve that I just renamed the common fields. So my doubt is how can i get the common fields which will include data from mutiple tables. Or as per your explanation QV will take care automatically.

               

              My changed script is something like :

               

              Country1:

              LOAD Country as country1,

                   Capital as capital1,

                   [Area(km.sq)] as area1,

                   [Population(mio)] as population1,

                   [Pop. Growth],

                   Currency as currency1,

                   Inflation as inflation1,

                   [Official name of Country] as off_name1

              FROM

              [C:\Users\Admin\Desktop\QV\Sujeeth panda\Data Sources\Country1.csv]

              (txt, codepage is 1252, embedded labels, delimiter is ',', msq);

               

               

               

               

              Country2:

              LOAD Country as country2,

                   Capital as capital2,

                   [Area(km.sq)] as area2,

                   [Population(mio)] as population2,

                   [Pop. Growth] ,

                   Currency as currency2,

                   Inflation as inflation2,

                   [Official name of Country] as off_name2

              FROM

              [C:\Users\Admin\Desktop\QV\Sujeeth panda\Data Sources\Country2.csv]

              (txt, codepage is 1252, embedded labels, delimiter is ',', msq);

               

               

               

               

              Country3:

              LOAD Country as country3,

                   [Official name of Country] as off_name3,

                   [Area(km.sq)] as area3

              FROM

              [C:\Users\Admin\Desktop\QV\Sujeeth panda\Data Sources\Country3.csv]

              (txt, codepage is 1252, embedded labels, delimiter is ',', msq);

               

               

              So, if i want to create a list box for country and capital then how I am going to get data from multiple table.

               

              Please guide me.

               

              Thanks,

              sujeet

                • Re: How to join two tables
                  Stefan Wühl

                  The only issue seems to be your third table, where some fields are missing.

                   

                  You can force QV to also concatenate this table by using CONCATENATE LOAD prefix.

                   

                  Country3:

                  CONCATENATE LOAD Country ,

                       [Official name of Country] ,

                       [Area(km.sq)] as area3

                  FROM

                  [C:\Users\Admin\Desktop\QV\Sujeeth panda\Data Sources\Country3.csv]

                  (txt, codepage is 1252, embedded labels, delimiter is ',', msq);

                   

                  Now you should only have one table in your data model, containing all records.

                  For sure, if you select a Country from source Country3.csv, you won't be able to see the Capital or other missing information.

                   

                  You can use a where clause with exists() function to only load non previously loaded Countries.

                   

                  Or maybe I am still missing your issue?