How to concatenate 2 tables

[sersal10]

Hi,

I try to concatenate these 2 tables and the fields from the budget one (.xlsx) results in nulls as you can see in the image attatched, where I know there are values. Is there something wrong? 

Many thanks!!!

This is the code: 

LIB CONNECT TO 'Amazon_Redshift_redshift-dwh.cwafb1bxivm3.eu-west-1.redshift.amazonaws.com';

budget:

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]);

concatenate (budget)

LOAD "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;

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";

[BrunPierre]

Hi, there's nothing wrong with the resultant table as when concatenating, all non-matching fields between the tables will be assigned null values.

What is the requirement here?

[sersal10]

Hi BrunPierre!

Thank you for your answer.

I think you are correct. I removed the weeks_left_to_event and event_code from the table I provided attatched to the previous post, and I see all the data. 

So maybe the problem is that the name of the columns event_code and weeks_left_to_event are shared in both tables and need to be the same name or maybe the type of data? How would you address it?

Many thanks

[sersal10]

Sorry, what I finally need is an inner join, but not sure about the sintaxys in Qlik. It seems there is an error at where I put the text in bold.

This is the code:

LIB CONNECT TO 'Amazon_Redshift_redshift-dwh.cwafb1bxivm3.eu-west-1.redshift.amazonaws.com';

budget:

LOAD

    text("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]);

ticketing:

LOAD "event_id",

    "event_name",

    "event_country",

    "event_date",

    capacity,

    text("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;

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";

TablaFinal:

INNER JOIN budget, event_ticketing

ON budget.event_code = event_ticketing.event_code

AND budget.weeks_left_to_event = event_ticketing.weeks_left_to_event;

/* Selecciona y renombra las columnas que necesites */

LOAD 

    budget.event_code as event_code_you,

    event_ticketing.event_name,   

    budget.budget_quantity as quantity,

    budget.budget_gross_revenue as budget_q, 

    event_ticketing.weeks_left_to_event

FROM TablaFinal;

[BrunPierre · Accepted solution]

As below:

budget:
LOAD *,
"Código del evento" as event_code,
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]);

Inner Join

ticketing:
LOAD *;
SQL SELECT *
FROM "dbt_xavi_marts"."marts_event_ticketing";