6 Replies Latest reply: Mar 1, 2012 2:05 PM by gerardo.pauza RSS

    inner join between two tables

      Hi everyone,

             i need to load a new qvw data with information from two tables, something like this but in a qlikview file:

       

      "select users.username, users.email  from users inner join deals

      on deals.seller_id=users.id

      group by users.email"

       

      i tried to write something like that in qlikview but i had an error loading the qvw.

       

      Could you give an advice?

       

      The qlikview file look like this without the condition i wrote before:

       

      SELLERS:

      LOAD `available_balance_amount`,

          `available_points`,

          `bac_tourism_id`,

          `bac_user_id`,

          `blocked_amount`,

          `cookie_hash`,

          `cookie_time_modified`,

          created,

          email,

          `fb_user_id`,

          `fibertel_user_id`,

          `gift_user_id`,

          `has_buyed`,

          id,

          `is_active`,

          `is_agree_terms_conditions`,

          `is_email_confirmed`,

          `is_openid_register`,

          `last_logged_in_time`,

          `last_login_ip`,

          modified,

          password,

          `referred_by_user_id`,

          `referred_date`,

          `signup_ip`,

          `twitter_access_key`,

          `twitter_access_token`,

          `twitter_user_id`,

          `user_login_count`,

          `user_openid_count`,

          `user_type_id`,

          `user_view_count`,

          username,

          `wallet_blocked`;

      SQL SELECT `available_balance_amount`,

          `available_points`,

          `bac_tourism_id`,

          `bac_user_id`,

          `blocked_amount`,

          `cookie_hash`,

          `cookie_time_modified`,

          created,

          email,

          `fb_user_id`,

          `fibertel_user_id`,

          `gift_user_id`,

          `has_buyed`,

          id,

          `is_active`,

          `is_agree_terms_conditions`,

          `is_email_confirmed`,

          `is_openid_register`,

          `last_logged_in_time`,

          `last_login_ip`,

          modified,

          password,

          `referred_by_user_id`,

          `referred_date`,

          `signup_ip`,

          `twitter_access_key`,

          `twitter_access_token`,

          `twitter_user_id`,

          `user_login_count`,

          `user_openid_count`,

          `user_type_id`,

          `user_view_count`,

          username,

          `wallet_blocked`

      FROM clubcupon.users;

       

      STORE SELLERS INTO ..\QVD\SELLERS.QVD;

      DROP TABLE SELLERS;

       

      Thanks

      gerardo

        • Re: inner join between two tables
          Celambarasan Adhimulam

          Hi,

               Have a common fieldname in both tables which only enable you to join a 2 tables.

           

          deals:

          Load

               *

          From..;\\Already you have loaded

          users:

          Load

          *

          from.. ; \\Already you have loaded

          OutputTable:

          Load Distinct

                    id,

                    username,

                    email

          Resident

               users;

          inner join(OutputTable)

          Load

                   seller_id as id

          Resident

          deals;

           

          Now output table has the required data.

           

          Hope it helps

          Celambarasan

            • Re: inner join between two tables

              Hi Celambarasan,

               

                      I tried to do what you said but this give me all users_id without this condition clubcupon.users.id = clubcupon.deals.seller_id

               

               

               

              This is my new load script. Any idea?

               

               

               

              DEALS:

              Load `seller_id`;

              SQL SELECT `seller_id`

              FROM clubcupon.deals;

               

              USERS:

              LOAD `available_balance_amount`,

                  `available_points`,

                  `bac_tourism_id`,

                  `bac_user_id`,

                  `blocked_amount`,

                  `cookie_hash`,

                  `cookie_time_modified`,

                  created,

                  email,

                  `fb_user_id`,

                  `fibertel_user_id`,

                  `gift_user_id`,

                  `has_buyed`,

                  id,

                  `is_active`,

                  `is_agree_terms_conditions`,

                  `is_email_confirmed`,

                  `is_openid_register`,

                  `last_logged_in_time`,

                  `last_login_ip`,

                  modified,

                  password,

                  `referred_by_user_id`,

                  `referred_date`,

                  `signup_ip`,

                  `twitter_access_key`,

                  `twitter_access_token`,

                  `twitter_user_id`,

                  `user_login_count`,

                  `user_openid_count`,

                  `user_type_id`,

                  `user_view_count`,

                  username,

                  `wallet_blocked`;

              SQL SELECT `available_balance_amount`,

                  `available_points`,

                  `bac_tourism_id`,

                  `bac_user_id`,

                  `blocked_amount`,

                  `cookie_hash`,

                  `cookie_time_modified`,

                  created,

                  email,

                  `fb_user_id`,

                  `fibertel_user_id`,

                  `gift_user_id`,

                  `has_buyed`,

                  id,

                  `is_active`,

                  `is_agree_terms_conditions`,

                  `is_email_confirmed`,

                  `is_openid_register`,

                  `last_logged_in_time`,

                  `last_login_ip`,

                  modified,

                  password,

                  `referred_by_user_id`,

                  `referred_date`,

                  `signup_ip`,

                  `twitter_access_key`,

                  `twitter_access_token`,

                  `twitter_user_id`,

                  `user_login_count`,

                  `user_openid_count`,

                  `user_type_id`,

                  `user_view_count`,

                  username,

                  `wallet_blocked`

              FROM clubcupon.users;

               

              SELLERS:

              Load Distinct

                        id,

                        username,

                        email

              Resident USERS;

              inner join(SELLERS)

              Load seller_id as id

              Resident DEALS;

               

              STORE SELLERS INTO ..\QVD\SELLERS.QVD;

               

               

               

               

               

              De: Celambarasan Adhimulam qliktech@sgaur.hosted.jivesoftware.com

              Enviado el: miércoles, 29 de febrero de 2012 09:18 a.m.

              Para: Pauza, Gerardo

              Asunto: - Re: inner join between two tables

               

               

               

               

              QlikCommunity <http://community.qlik.com/index.jspa>

               

               

              Re: inner join between two tables

               

               

              created by Celambarasan Adhimulam <http://community.qlik.com/people/celambarasan>  in New to QlikView - View the full discussion <http://community.qlik.com/message/196612#196612