Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Not applicable

join two tables

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

Tags (1)
13 Replies
sinanozdemir
Valued Contributor III

Re: join two tables

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

Not applicable

Re: join two tables

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

Employee
Employee

Re: join two tables

Hello Kumar,

Are you using Qlik Sense or QlikView?

Mike

Regards,
Mike Tarallo
Qlik
sinanozdemir
Valued Contributor III

Re: join two tables

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.

Not applicable

Re: join two tables

Hi mike,

I am using Qlik sense.

If you have understand the problem i am facing please help on it.

Regards,

Kumar

Not applicable

Re: join two tables

Hi Sinan,

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

Regards,

Pramod

sinanozdemir
Valued Contributor III

Re: join two tables

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

Not applicable

Re: join two tables

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

sinanozdemir
Valued Contributor III

Re: join two tables

Hi Kumar,

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

Thanks

Community Browser