13 Replies Latest reply: Sep 15, 2015 8:51 AM by Sinan Ozdemir RSS

    join two tables

    Kumar Pramod

      I have attaching the sample of two tables i need to join both.

       

      signupavtivity table

      client_iduser_idemail_type_iduser_signedinvitation_id

       

      useractivity table

      client_iduser_idemail_type_id

       

       

      i need to join the above two table.

       

      I am using a below script:

       

      LOAD `invitation_id`,

          `user_signed`,

          landing_page_accessed,

          referrer_email_type_id as email_type_id,

          SQL SELECT `invitation_id`,

           referrer_email_type_id,

          `user_signed`,

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

      join

      LOAD

          user_id,

          status as email_type_id;

      SQL SELECT

           user_id,

          status

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

       

      after i join the rows added from useractivity is not have the values in columns invitation_id,user_signed,client_id.

       

      how can i add, after i join it has to take all the values for the respective field through user_id.

       

      In signupactivitylog i am not able to add the user_id. if i add the user_id it is showing sync table error.

       

      i need the count of email_type_id for particular client.

       

       

      Regards,

      Pramod

        • Re: join two tables
          Sinan Ozdemir

          Hi Pramod,

           

          the join statement between two tables is superfluous because Qlik does an outer join by default so the join does the same outer join unless you specify left or right.

           

          I just changed the name of email_type_id to email_type in signup activity table, it seems to me that it is working:

          Capture.PNG

          Capture.PNG

          I am attaching qvw in case you need it.

           

          Hope this helps

           

          Thanks

            • Re: join two tables
              Kumar Pramod

              Hi Sinan,

               

              Thanks for the response.

               

              That's fine.

              Now i need a count of email_type_id as below.

              email_type_id   email_description                count
              2  Signed-Up First Reminder25
              3  Signed-Up Second Reminder36
              4   Signed-Up Third Reminder12
              8   Signed-Up Fourth Reminder45
              44   InvitationEmail75
              45   Not Signed First Reminder12
              46   Not Signed Second Reminder17
              47   Not Signed Third Reminder22
              126   Not Signed Fourth Reminder13

               

              now email_type_id 2,3,4,8 in user_activity table and

              email_type_id 44,45,46,47 in signup_activity table. 

               

              How can i join them and have them in one column and it's corresponding count in next column.

               

              Regards,

              Pramod

                • Re: join two tables
                  Sinan Ozdemir

                  Hi Pramod,

                   

                  It seems like I need to download the new version of QlikSense and I am having a difficulty with it, but the below solution should apply to QlikSense as well:

                   

                  You can use Concatenate:

                  Capture.PNG

                  And in the front-end, you can create a table and count client ids per email_type(of course depending on your requirement, it is either distinct or non-distinct count):

                  Capture.PNG

                   

                  Hope this helps.

                    • Re: join two tables
                      Kumar Pramod

                      Hi Sinan,

                       

                      I tried with the concatenate but i am getting circular reference warnings/error.

                       

                      Regards,

                      Pramod

                        • Re: join two tables
                          Sinan Ozdemir

                          Hi Kumar,

                           

                          I used QlikSense this time and I am not getting circular reference error:

                          Capture.PNG

                          Here is the front-end table:

                          Capture1.PNG

                          I am also attaching the qvd file.

                           

                          Hope this helps.

                           

                          Thanks

                            • Re: join two tables
                              Kumar Pramod

                              Hi Sinan,

                               

                              Thanks for the reply.

                               

                              Sorry In the script i have some more tables i have not mentioned it.

                              Can you see my script, I have added complete script.

                               

                              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 rcdbprod.INVITATION WHERE client_id in (6,12,13,15,16,3,8,10,18);

                               

                              LOAD `client_id`,

                                   `client_name`;

                              SQL SELECT `client_id`,

                                  `client_name`

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

                               

                              LOAD `batch_meta_data_id`,

                                   date((start_date),'MM-DD-YYYY') as start_date,

                                  `batch_name`;

                              SQL SELECT `batch_meta_data_id`,

                                  `batch_name`,

                                  start_date

                              FROM rcdbprod.BATCHMETADATA 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 rcdbprod.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 rcdbprod.SIGNUPACTIVITYLOG WHERE client_id in (6,12,13,15,16,3,8,10,18);

                               

                              Concatenate

                              LOAD

                                  client_id,

                                  status as email_type_id ;

                              SQL SELECT

                                  client_id,

                                  status

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

                               

                              LOAD `batch_meta_data_id`,

                                  `campaign_metrics_id`,

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

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

                                  `landing_page_hits`,

                                  `sign_up`,

                                  completions,

                                  fails,

                                  partials,

                                  num_required_subjects;

                              SQL SELECT

                                 `batch_meta_data_id`,

                                   `campaign_metrics_id`,

                                  `campaign_start_date`,

                                  `campaign_end_date`,

                                  `landing_page_hits`,

                                  `sign_up`,

                                  completions,

                                  fails,

                                  partials,

                                  num_required_subjects

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

                               

                              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;

                               

                               

                              LOAD *,

                              If(total_time_taken<='00:01:00' ,Dual('00:00-01:00',0),

                              If(total_time_taken>'00:60:00',Dual('>60:00',60),

                                Dual(Num(Floor(total_time_taken*1440), '00') & ':00-' & Num(Floor(total_time_taken*1440+1), '00'), Floor(total_time_taken*1440))))

                              as time_interval;

                              LOAD `subject_id`,

                                  `subject_name`,

                                  `lender_offer_id`,

                                  `subject_status`,

                                  version,

                                  last_accessed_page,

                                  SubField(If(Match(version, 'v2'), last_accessed_page), ',', -1) as v2,

                                  Interval(Alt(

                                  Interval#(total_time_taken,'hh:mm:ss.ff'),

                                      Interval#(total_time_taken,'hh:mm:ss')

                                      ),'hh:mm:ss.fff') as total_time_taken,

                                  user_id,

                                  date((`subject_completed_date`),'MM-DD-YYYY') as `subject_completed_date`;

                              SQL SELECT `subject_id`,

                                  `subject_name`,

                                    `lender_offer_id`,

                                  `subject_status`,

                                  last_accessed_page,

                                  version,

                                  total_time_taken,

                                  user_id,

                                 `subject_completed_date`

                              FROM rcdbprod.SUBJECTACCESSTRACKING;

                               

                               

                              LOAD [question_id],

                                   'Q' & question_id AS Q_id,

                                  'Q' & If(fmod(question_id , 10) = 0, 10, fmod(question_id , 10)) AS QNoMod,

                                [is_correct_answer],

                                  [lender_offer_id],

                                [test_type];

                              SQL SELECT  `question_id`,

                                `is_correct_answer`,

                                   `lender_offer_id`,

                                `test_type`

                              FROM `rcdbprod`.`USERQUESTION`;

                               

                               

                              LOAD `batch_meta_data_id`,

                                  browser,

                                  `Type` as browser_type,

                                  `visits` as browser_visits;

                              SQL SELECT `batch_meta_data_id`,

                                  browser,

                                  `Type`,

                                  `visits`

                              FROM rcdbprod.BROWSERSTATS;

                               

                               

                              LOAD `batch_meta_data_id`,

                                  `operating_system`,

                                  `Type` as os_type,

                                    visits as os_visits;

                              SQL SELECT `batch_meta_data_id`,

                                  `operating_system`,

                                  `Type`,

                                  visits

                              FROM rcdbprod.OPERATINGSYSTEMSTATS;

                               

                              LOAD `email_type_id`,

                                  `email_description`;

                              SQL SELECT `email_type_id`,

                                  `email_description`

                              FROM rcdbprod.EMAILTYPES where email_type_id in (2,3,4,8,44,45,46,47,126);

                               

                              LOAD `invitation_id`,

                                  `invitation_dispatch_channel_id`,

                                  `dispatched_by`;

                              SQL SELECT `invitation_id`,

                                  `invitation_dispatch_channel_id`,

                                  `dispatched_by`

                              FROM rcdbprod.INVITATIONACTIVITYLOG;

                               

                               

                              Regards,

                              Pramod

                                • Re: join two tables
                                  Sinan Ozdemir

                                  Hi Kumar,

                                   

                                  Can you get to the data model view and post the screenshot so that I can see it a bit better?

                                   

                                  Thanks

                                    • Re: join two tables
                                      Kumar Pramod

                                      HI Sinan,

                                       

                                      I am attaching the image of data model view.

                                       

                                      Please check and help on it.

                                       

                                      Thanks,

                                      Pramod

                                        • Re: join two tables
                                          Sinan Ozdemir

                                          Hey Kumar,

                                           

                                          Can you do the below in both DateBridge and Signupactivitylog tables?

                                           

                                          DateBridge:

                                          invitation_id as invit_id

                                           

                                          Signupactivitylog:

                                          invitation_id,

                                          invitation_id as invit_id

                                           

                                          So I think Signupactivitylog table is being referenced by the same field twice, that could be the reason so you are totally renaming invitation_id to invit_id in DateBridge table and you are keeping invitation_id in Signupactivitylog, but bringing it again and renaming it to invit_id. As a result of this, DateBridge gets hooked onto Signupactivitylog on invit_id and Signupactivitylog gets hooked onto Coursecompletion on invitation_id.

                                           

                                          Hope this helps if not, would you mind attaching qvd? of course if it is not confidential.

                                           

                                          Thanks

                          • Re: join two tables
                            Michael Tarallo

                            Hello Kumar,

                             

                            Are you using Qlik Sense or QlikView?

                             

                            Mike