join two different columns from different tables.

[#1]

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

[alexandros17]

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;

[Anonymous]

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

[#4]

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

[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

[#6]

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

[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

[Anonymous]

Hi Pramod,

They are no invitation ids field in files attached?

[#9]

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

[#10]

hi you check with this.