Unable to use inner join function
3 views (last 30 days)
Show older comments
I try to join 2 tables together based on an common ID.
When i use the innerjoin function I do not get an error but just a new table without rows.
%% import trips
opts = delimitedTextImportOptions("NumVariables", 28);
% Specify range and delimiter
opts.DataLines = [2, Inf];
opts.Delimiter = ",";
% Specify column names and types
opts.VariableNames = ["id", "country_id", "location_id", "age_group_id", "trip_name", "seo_url", "short_details", "is_featured", "trip_letter", "show_trip_letter", "trip_type", "trip_main_image", "trip_thumb_image", "start_date", "duration", "trip_fee", "trip_discount", "original_fee", "trip_seats_status", "marketing_text", "is_not_bookable", "meta_title", "meta_description", "meta_keyword", "archive", "is_full", "status", "ts"];
opts.VariableTypes = ["double", "double", "double", "double", "categorical", "string", "string", "double", "categorical", "double", "categorical", "string", "string", "datetime", "double", "double", "double", "double", "double", "string", "double", "string", "string", "string", "double", "double", "double", "datetime"];
% Specify file level properties
opts.ExtraColumnsRule = "ignore";
opts.EmptyLineRule = "read";
% Specify variable properties
opts = setvaropts(opts, ["seo_url", "short_details", "trip_main_image", "trip_thumb_image", "marketing_text", "meta_title", "meta_description", "meta_keyword"], "WhitespaceRule", "preserve");
opts = setvaropts(opts, ["trip_name", "seo_url", "short_details", "trip_letter", "trip_type", "trip_main_image", "trip_thumb_image", "marketing_text", "meta_title", "meta_description", "meta_keyword"], "EmptyFieldRule", "auto");
opts = setvaropts(opts, "start_date", "InputFormat", "yyyy-MM-dd");
opts = setvaropts(opts, "ts", "InputFormat", "yyyy-MM-dd HH:mm:ss");
% Import the data
trips = readtable("/Users/simireizen/Documents/MATLAB/trips.csv", opts);
%% Import Bookings
opts = delimitedTextImportOptions("NumVariables", 130);
% Specify range and delimiter
opts.DataLines = [2, Inf];
opts.Delimiter = ",";
% Specify column names and types
opts.VariableNames = ["VarName1", "id", "VarName3", "trip_id", "VarName5", "client_id", "VarName7", "travel_agent_id", "VarName9", "travel_brand_id", "VarName11", "child_firstname", "VarName13", "child_lastname", "VarName15", "gender", "VarName17", "child_dob", "VarName19", "parent_name", "VarName21", "parent_email", "VarName23", "email", "VarName25", "address", "VarName27", "house_number", "VarName29", "city", "VarName31", "postcode", "VarName33", "telephone", "VarName35", "cellphone", "VarName37", "whatsapp_number", "VarName39", "location_pickup_id", "VarName41", "child_diet", "VarName43", "child_medication", "VarName45", "about_child", "VarName47", "date_added", "VarName49", "can_drive", "VarName51", "have_driving_license", "VarName53", "have_creditcard", "VarName55", "trip_fee", "VarName57", "discount_id", "VarName59", "discount_amount", "VarName61", "insurance", "VarName63", "cancellation_insurance", "VarName65", "travel_insurance", "VarName67", "cancellation_policy_number", "VarName69", "travel_policy_number", "VarName71", "survival_adventure_insurance", "VarName73", "insurance_admin_charges", "VarName75", "nature_disaster_insurance", "VarName77", "sgr_contribution", "VarName79", "insurnace_question_1", "VarName81", "insurnace_question_2", "VarName83", "total_amount", "VarName85", "paid_amount", "VarName87", "deleted", "VarName89", "payment_reminder_email_sent", "VarName91", "total_reminder_sent", "VarName93", "email_sent", "VarName95", "login_reminder_email_sent", "VarName97", "upsell_email_sent", "VarName99", "deposit_reminder_email_sent", "VarName101", "passport_reminder_email_sent", "VarName103", "display_name", "VarName105", "additional_address", "VarName107", "contact_person_name", "VarName109", "contact_person_extra_name", "VarName111", "contact_person_extra_cellphone", "VarName113", "travel_agent_email", "VarName115", "commission", "VarName117", "covid_option", "VarName119", "account_name", "VarName121", "account_number", "VarName123", "phone_reminder_email_sent", "VarName125", "status", "VarName127", "ts", "VarName129", "trip_date_before_42"];
opts.VariableTypes = ["double", "double", "double", "double", "double", "double", "double", "double", "double", "double", "string", "string", "string", "string", "categorical", "categorical", "datetime", "datetime", "double", "string", "double", "string", "string", "string", "string", "string", "double", "double", "string", "string", "string", "string", "double", "double", "double", "double", "double", "double", "double", "double", "categorical", "categorical", "string", "string", "string", "string", "datetime", "datetime", "double", "double", "double", "double", "double", "double", "double", "double", "double", "double", "double", "double", "categorical", "categorical", "double", "double", "double", "double", "double", "double", "double", "double", "double", "double", "double", "double", "double", "double", "double", "double", "double", "double", "double", "double", "double", "double", "double", "double", "double", "double", "double", "double", "double", "double", "double", "double", "double", "double", "double", "double", "double", "double", "double", "double", "string", "string", "double", "string", "string", "string", "string", "string", "double", "double", "double", "string", "double", "double", "double", "double", "string", "string", "string", "string", "double", "double", "double", "double", "datetime", "datetime", "datetime", "datetime"];
% Specify file level properties
opts.ExtraColumnsRule = "ignore";
opts.EmptyLineRule = "read";
% Specify variable properties
opts = setvaropts(opts, ["VarName11", "child_firstname", "VarName13", "child_lastname", "parent_name", "parent_email", "VarName23", "email", "VarName25", "address", "VarName29", "city", "VarName31", "postcode", "VarName43", "child_medication", "VarName45", "about_child", "VarName103", "display_name", "additional_address", "VarName107", "contact_person_name", "VarName109", "contact_person_extra_name", "travel_agent_email", "VarName119", "account_name", "VarName121", "account_number"], "WhitespaceRule", "preserve");
opts = setvaropts(opts, ["VarName11", "child_firstname", "VarName13", "child_lastname", "VarName15", "gender", "parent_name", "parent_email", "VarName23", "email", "VarName25", "address", "VarName29", "city", "VarName31", "postcode", "VarName41", "child_diet", "VarName43", "child_medication", "VarName45", "about_child", "VarName61", "insurance", "VarName103", "display_name", "additional_address", "VarName107", "contact_person_name", "VarName109", "contact_person_extra_name", "travel_agent_email", "VarName119", "account_name", "VarName121", "account_number"], "EmptyFieldRule", "auto");
opts = setvaropts(opts, "VarName17", "InputFormat", "yyyy-MM-dd");
opts = setvaropts(opts, "child_dob", "InputFormat", "yyyy-MM-dd");
opts = setvaropts(opts, "VarName47", "InputFormat", "yyyy-MM-dd");
opts = setvaropts(opts, "date_added", "InputFormat", "yyyy-MM-dd");
opts = setvaropts(opts, "VarName127", "InputFormat", "yyyy-MM-dd HH:mm:ss");
opts = setvaropts(opts, "ts", "InputFormat", "yyyy-MM-dd HH:mm:ss");
opts = setvaropts(opts, "VarName129", "InputFormat", "yyyy-MM-dd");
opts = setvaropts(opts, "trip_date_before_42", "InputFormat", "yyyy-MM-dd");
opts = setvaropts(opts, ["insurnace_question_1", "insurnace_question_2", "VarName105"], "TrimNonNumeric", true);
opts = setvaropts(opts, ["insurnace_question_1", "insurnace_question_2", "VarName105"], "ThousandsSeparator", ",");
% Import the data
bookings = readtable("/Users/simireizen/Documents/MATLAB/SimiScript/deleted_bookings.csv", opts);
%% join tables
T = innerjoin(bookings,trips);
0 Comments
Accepted Answer
Stephen23
on 10 Aug 2022
Edited: Stephen23
on 10 Aug 2022
Because the two tables use different names for the key variable, you will have to help INNERJOIN by telling it the names of the variables/columns that you want to use for the keys. For example:
T = innerjoin(bookings,trips,'LeftKeys','trip_id', 'RightKeys','id');
0 Comments
More Answers (0)
See Also
Categories
Find more on Categorical Arrays in Help Center and File Exchange
Community Treasure Hunt
Find the treasures in MATLAB Central and discover how the community can help you!
Start Hunting!