Hi all
I need to left join these 2 tables so that the columns from the budget table joins to the first one when event_code and weeks_left_to_event are the same. I came up with this code but doesn´t work.
Many thanks!!!
This is the code:
LIB CONNECT TO 'Amazon_Redshift_redshift-dwh.cwafb1bxivm3.eu-west-1.redshift.amazonaws.com';
budgets:
LOAD
"Código del evento" as event_code,
event_end_time as budget_event_end_time,
timestamp(FECHA) AS budget_date,
"NUMERO DE TICKETS" as budget_quantity,
PRECIO as budget_price,
"BOOKING FEE" as budget_fee,
"GROSS REVENUE" as budget_gross_revenue,
round(num(Interval(event_end_time - timestamp(FECHA), 'DD'))/7,2) AS weeks_left_to_event
FROM [lib://DataFiles/Ticketing 2024.xlsx]
(ooxml, embedded labels, table is [Hoja 3]);
left join
ON
budgets."Código del evento" = [marts_event_ticketing]."event_code"
AND
budgets.weeks_left_to_event = [marts_event_ticketing]."weeks_left_to_event";
LOAD
"event_code",
"event_id",
"event_name",
"event_country",
"event_date",
capacity,
"event_code",
"ticket_id",
"cashless_paid_credits",
quantity,
"ticket_type",
"sold_date",
"ticket_date",
"ticket_category",
status,
"event_currency",
"gbp_rate",
"eur_rate",
"base_amount",
"fee_amount",
"total_amount",
email,
"customer_country",
city,
age,
gender,
"age_group",
"year_event",
"month_event",
"days_left_to_event",
"weeks_left_to_event",
"months_left_to_event",
babb,
"previous_event_code",
"event_end_time",
"event_to_compare",
"previous_day_sales",
"marketing_total_spend",
cpa;
[marts_event_ticketing]:
SELECT "event_id",
"event_name",
"event_country",
"event_date",
capacity,
"event_code",
"ticket_id",
"cashless_paid_credits",
quantity,
"ticket_type",
"sold_date",
"ticket_date",
"ticket_category",
status,
"event_currency",
"gbp_rate",
"eur_rate",
"base_amount",
"fee_amount",
"total_amount",
email,
"customer_country",
city,
age,
gender,
"age_group",
"year_event",
"month_event",
"days_left_to_event",
"weeks_left_to_event",
"months_left_to_event",
babb,
"previous_event_code",
"event_end_time",
"event_to_compare",
"previous_day_sales",
"marketing_total_spend",
cpa
FROM "dbt_xavi_marts"."marts_event_ticketing";
