4 Replies Latest reply: Aug 18, 2012 10:08 AM by Stefan Wühl RSS

    Joining Table Issue

    Nihhal L

      Hi Experts,

       

      Table1

      invno, city, t1_amount

      1110, Blr, 1000

      1120, tn, 2000

      1130, mum, 3000

      Table 2

      invno, city, t2_amount

      1110, tn, 4000

      1120, mum, 5000

      1130, blr, 6000

       

      I want to display in my application like

       

      city, t1_amount, t2_amount

      blr, 1000, 6000

      tn, 2000, 4000

      mum, 3000, 5000

       

      How can i do please share solution.

        • Re: QlikView Complex Task
          Stefan Wühl

          You can try concatenating your two tables:

           

          LOAD invno, city, t1_amount FROM Table1;

           

          CONCATENATE LOAD invno, city, t2_amount FROM Table2;

            • Re: QlikView Complex Task
              Nihhal L

              I tried, i did not get same output.

                • Re: QlikView Complex Task
                  Sunil Chauhan

                  use below code

                  table1:

                  Load * inline [

                  invno, city, t1_amount

                  1110, Blr, 1000

                  1120, tn, 2000

                  1130, mum, 3000

                  ];

                   

                   

                  Table2:

                  Load * inline [

                  invno, city, t2_amount

                  1110, tn, 4000

                  1120, mum, 5000

                  1130, Blr, 6000

                  ];

                   

                   

                  table3:

                  load city, t1_amount,

                  1 as Key

                  resident table1;

                  Concatenate

                  load  city, t2_amount,

                  1 as Key1

                  resident Table2;

                   

                   

                   

                  drop tables  table1,Table2;

                   

                   

                  and take a pibot table

                   

                  city in dimension

                  and in expression

                   

                  sum(t1_amount)

                  sum(t2_amount)

                   

                  name in table should be in same case

                   

                  hope this help

                  • Re: QlikView Complex Task
                    Stefan Wühl

                    Nihhal,

                     

                    what do you get then?

                     

                    If I do a CONCATENATE LOAD like described above, then create a table chart with dimension city and two expressions

                    =sum(t1_amount)

                    and

                    =sum(t2_amount)

                     

                    I do get the table you requested above (note that wrote one time Blr and one time blr as city name value, which QV will take as two distinct values. I assume this is just a typo, but you can use

                     

                    lower(city) as city,

                     

                    in your loads to get all city names in lower case.

                     

                    If you want to get a table in your data model (not in the frontend) like you shown above, you can do the concatenate load, the do a group by city:

                     

                     

                     

                    table1:

                    Load * inline [

                    invno, city, t1_amount

                    1110, Blr, 1000

                    1120, tn, 2000

                    1130, mum, 3000

                    ];

                     

                     

                    Table2:

                    Concatenate Load * inline [

                    invno, city, t2_amount

                    1110, tn, 4000

                    1120, mum, 5000

                    1130, Blr, 6000

                    ];

                     

                    LOAD city,

                    sum(t1_amount) as t1_amount,

                    sum(t2_amount) as t2_amount

                    Resident table1 group by city;

                     

                    drop table table1;

                     

                    which is basically the same idea as with the table chart, only all be done in the data model.

                     

                    It really depends what you want to do with your data, if you need to keep the original information or not.

                     

                    Regards,

                    Stefan