How to concatenate 2 tables in the load editor

[sersal10]

Hi all,

I need to concatenate these 2 tables, and they share the name of 2 fields (event_code and weeks_left_to_event), but need them to be connected only by the event_code. 

As I have the script now, there aer rows missing form the second table as only appear the rows that share the weeks_left_to_event. How could I do this? Do I need to make a UNION ALL in the pipeline before Qlik or there is a way to do it in the load editor?

This is the code:

[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",
"previous_event_name",
"previous_year_comparison",
"ticket_sales",
"previous_event_ticket_sales",
"event_end_time",
"event_to_compare",
"previous_day_sales",
"marketing_total_spend",
cpa,
budget_event_end_time,
budget_quantity,
budget_price,
budget_fee,
budget_gross_revenue
FROM "dbt_xavi_marts"."marts_event_ticketing";

concatenate
LOAD
"event_code",
"budget_event_end_time",
round("weeks_left_to_event", 1) AS weeks_left_to_event,
"budget_quantity",
"budget_price",
"budget_fee",
"budget_gross_revenue"
FROM [lib://DataFiles/Ticketing 2024 (1).xlsx]
(ooxml, embedded labels, table is [Hoja 3]);

[PrashantSangle]

use Join, if you want records from both table. if you want from one table then use left / right join according to your need

to avoid create key basis on 2 field, rename one of them field with some different name.

so in your case

Table1:

...

..

join

LOAD
"event_code",
"budget_event_end_time",
round("weeks_left_to_event", 1) AS weeks_left_to_event_new,
"budget_quantity",
"budget_price",
"budget_fee",
"budget_gross_revenue"
FROM [lib://DataFiles/Ticketing 2024 (1).xlsx]
(ooxml, embedded labels, table is [Hoja 3]);

Regards,

Prashant Sangle

[PhanThanhSon]

Hi,

If you just want to connect both table via event_code as Key then do this:

Qualify *;
Unqualify '%*';

[marts_event_ticketing]:
SELECT *,
event_code AS %KEY
FROM "dbt_xavi_marts"."marts_event_ticketing";

[Ticketing 2024]:
LOAD
"event_code" AS %KEY,
round("weeks_left_to_event", 1) AS weeks_left_to_event,
FROM [lib://DataFiles/Ticketing 2024 (1).xlsx]
(ooxml, embedded labels, table is [Hoja 3]);

Unqualify *;

If you want to concatenate both table do this:

[marts_event_ticketing]:
SELECT *
FROM "dbt_xavi_marts"."marts_event_ticketing";

Concatenate([marts_event_ticketing])
LOAD *,
round("weeks_left_to_event", 1) AS weeks_left_to_event,
FROM [lib://DataFiles/Ticketing 2024 (1).xlsx]
(ooxml, embedded labels, table is [Hoja 3]);

Best regards Son

[sersal10]

Hi, 

Actually I need weeks_left_to_event to be the same name because it is going to be the dimension I will use in a graph. 

I need something like a union all. the rows I add from the second table will have a lot of nulls as it has much less fields. But it is just to add a line of a budget in a graph.

Cheers

[sersal10]

Hi, 

It seems it doesn't work.

I would need something like a UNION ALL in SQL.

Cheers

 

[PrashantSangle]

when you use union all or union, it will append one record after another. It will not give you one single line.

also, not sure why you need same name "week_left_to_event". You can rename in property.

Can you explain with small data?

Regards,

Prashant Sangle

[sersal10]

Hi, 

Yes, I will explain:

In one table I have these kind of rows: 

event_code | weeks_left_to_event | tickets_sold

er20             | 20                             | 1000

and in the other table:

event_code | weeks_left_to_event | budget

er20             |5                                 | 5000

I want to end up with this: 

event_code | weeks_left_to_event | tickets_sold | budget

er20             | 20                             | 1000            | NULL

er20             |5                                 | NULL.         | 5000

[PrashantSangle · Accepted solution]

ohh.. then just use concatenate. it will work for you.

[sersal10]

Yep, I am trying that. Fingers crossed, as I think I tried that first last week, and gave me an error.

Cheers

[sersal10]

Hi

It doesn't work. the budget value ends up being null always.