9 Replies Latest reply: Sep 4, 2015 7:05 AM by Kumar Pramod RSS

    join two different columns from different tables.

    Kumar Pramod

      Hi all,

       

      I have 2 different tables. the column name "type_id" from two tables. I need to join these columns from two tables.

       

      can any one explain how can i join these two columns.

       

      Regards,

      Pramod

        • Re: join two different columns from different tables.
          Alessandro Saccone

          If you have

           

          Table1 with columns A, B, C

          and

           

          Table2 with columns A, D, E then

           

          Load A,B,C resident Table1;

          join

          LOAD A,D,E resident Table2;

          • Re: join two different columns from different tables.
            neetha P

            hi kumar,

             

            Please post sample date,so can help perfectly.

            If both tables structures are same use concatenate and

            if both tables structures are different use join

             

            Regards

            Neetha

            • Re: join two different columns from different tables.
              Kumar Pramod

              Hi all,

               

              Alexandros17

              neetha_p

               

              I have attached a sample data.

              I have 4 different tables. i have added sample data of all the tables.

               

              I need to create a pivot table.

              Dimensions :1. Email_type_id should be my dimension, but it should display it's corresponding email_description in column..(emailtypes table)

               

              Measures:

              2. I need a count of all email_type_id (2,3,4,8,44,45,46,47,126) in the corresponding row.

               

              3. Need a count of all referrer_email_type_id where user_signed = 1. (referrer_email_type_id = email_type_id ).(signup_activitylog table)

               

              4. Need a count of all referrer_email_type_id where user_signed = 0. (referrer_email_type_id = email_type_id ).(signup_activitylog table)

               

              5. Need a count of all email_type_id where course_completed = 1. (this data is from completion table).

               

               

              The email_type_id  counts needs to get from two table:

              2,3,4,8 from user_activity table

              44,45,46,47 from signup_activitylog table.

               

              Please help on this.

              ask if any clarification needed.

               

              Regards,

              Kumar

              • Re: join two different columns from different tables.
                Colin Albert

                In most cases with QlikView you do not need to join data.

                QlikView will associate columns with the same name automatically.

                 

                If table 1 has columns A, B, C, D and table 2 has columns A, E, F, G

                 

                Then just load the data and the QlikView data model will link the tables on column A automatically.

                You do not need to specify any joins.

                 

                You can create a chart that shows A as a dimension with sum(B) or sum(F) as expressions

                  • Re: join two different columns from different tables.
                    Kumar Pramod

                    hi colin,

                    Thanks for the reply.

                     

                    The issue is i am joining the two table SIGNUP  and USERACTIVITY  to get the count of email_type_id.

                    In SIGNUP table i have 44,45,46,47 email_type_id's.

                    In USERACTIVITY table i have 2,3,4,8 email_type_id's.


                    I need the count of email_type_id's which the user has used, I need to display in pivot table.

                    In SIGNUP table i have "invitation_id" which is again linked with client table. so i am getting the count on clients basis.

                    But, the email_type_id's  of USERACTIVITY joined has no invitation_id's so its's not displaying the counts when i filtered through the client_id's.


                    You can see my script:

                     

                     

                    LOAD `invitation_id`,

                        `user_signed`,

                        referrer_email_type_id as email_type_id;

                    SQL SELECT `invitation_id`,

                         referrer_email_type_id,

                        `user_signed`

                    FROM SIGNUP WHERE client_id in (6,12,13,15,16,3,8,10,18);

                     

                    JOIN

                     

                    LOAD status,

                        status as email_type_id,

                        `log_type_id`;

                    SQL SELECT lender_offer_id,

                          status ,

                        `log_type_id`

                    FROM USERACTIVITY where status in (2, 3,4,8);

                     

                     

                     

                    Please help on this. you can ask any clarification needed.

                     

                    Regards,

                    Pramod

                      • Re: join two different columns from different tables.
                        Colin Albert

                        It may be better to use a mapping table and ApplyMap rather than joining the data.

                        Joining can cause the number of rows to increase in the final table if a value matches more than one record, this does not happen with applymap.

                         

                        Have a look at this post  Don't join - use Applymap instead

                          • Re: join two different columns from different tables.
                            Kumar Pramod

                            HI colin,

                             

                            I have used the below load script. you can see i have already used the mapping for SIGNUPACTIVITYLOG and other tables.

                            as for mapping at least one  fields must be identical in both the tables.

                            I have user_id in both the tables. but, if i add the user_id in SIGNUPACTIVITYLOG i am getting sync warnings.

                             

                             

                            can u look the script and help me to write the mapping for SIGNUPACTIVITYLOG and USERACTIVITYLOG

                             

                            LOAD `invitation_id`,

                                `client_id`,

                                `batch_meta_data_id`,

                                 activated,

                                `date_activated`,

                                 date(floor(date_created),'MM-DD-YYYY') as Date,

                                 date#(left(date_created,10),'YYYY-MM-DD') as date_created,

                                 time#(mid(date_created,12,8),'hh:mm:ss') as time_created,

                                 date(WeekStart(date_created), 'MMM DD') &' - '& date(WeekEnd(date_created), 'MMM DD')  as Week_Group,

                                 week(date_created) as week_number,

                                 MonthName(date_created) as Month_Year,

                                 weekstart(date_created) as weekstart,

                                 WeekDay(date_created) as WeekDay,

                                 `campaign_name_id`;

                            SQL SELECT `invitation_id`,

                                `client_id`,

                                `batch_meta_data_id`,

                                activated,

                                `date_activated`,

                                `date_created`,

                                `campaign_name_id`

                            FROM INVITATION WHERE client_id in (6,12,13,15,16,3,8,10,18);

                             

                            LOAD `course_completion_id`,

                                `invitation_id`,

                                `lender_offer_id`,

                                `final_test_status`,

                                percentage_score,

                                `course_completed`,

                                date(floor(date_completed),'MM-DD-YYYY') as date_completed;

                            SQL SELECT `course_completion_id`,

                                `invitation_id`,

                                `lender_offer_id`,

                                `final_test_status`,

                                `course_completed`,

                                percentage_score,

                                `date_completed`

                            FROM COURSECOMPLETION;

                             

                            LOAD `invitation_id`,

                                `user_signed`,

                                landing_page_accessed,

                                referrer_email_type_id as email_type_id,

                                date(floor(date_accessed),'MM-DD-YYYY') as date_accessed,

                               date(floor(date_signed),'MM-DD-YYYY') as date_signed;

                            SQL SELECT `invitation_id`,

                                 referrer_email_type_id,

                                `user_signed`,

                                `date_signed`,

                                date_accessed,

                                landing_page_accessed

                            FROM SIGNUPACTIVITYLOG WHERE client_id in (6,12,13,15,16,3,8,10,18);

                            join

                            LOAD

                                status,

                                status as email_type_id,

                                `log_type_id`;

                            SQL SELECT

                                  status ,

                                `log_type_id`

                            FROM USERACTIVITYLOG where status in (2,3,4,8);

                             

                            invitation_id2Date:

                                 Mapping Load invitation_id, Date Resident INVITATION ;

                            invitation_id2date_accessed:

                                 Mapping Load invitation_id, date_accessed Resident SIGNUPACTIVITYLOG ;

                               

                            DateBridge:

                             

                                 Load invitation_id, Applymap('invitation_id2Date',invitation_id,Null()) as CanonicalDate, 'invitation' as DateType

                                  Resident INVITATION;

                                    

                                 Load invitation_id, Applymap('invitation_id2date_accessed',invitation_id,Null()) as CanonicalDate, 'accessed' as DateType

                                      Resident SIGNUPACTIVITYLOG ;    

                             

                                 Load invitation_id, date_completed as CanonicalDate, 'completion' as DateType

                                      Resident COURSECOMPLETION;

                             

                             

                            Thanks,

                            pramod

                          • Re: join two different columns from different tables.
                            neetha P

                            Hi Pramod,

                             

                            They are no invitation ids field in files attached?