How to perform a UNION ALL in the load editor

[sersal10]

Hi all,

I want to concatenate 2 tables and I am trying this: 

marts_event_ticketing:
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
marts_event_ticketing:
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",
"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
FROM "processed_production_marts"."marts_event_ticketing";

RESIDENT marts_event_ticketing; (THIS APPEARS IN RED!!)

Drop Table marts_event_ticketing;

What I wrote in bold at the end is in red. 

When I try to load the data it says No qualified path for file:***

Any help appreciated!!

Thanks

[LRuCelver]

The RESIDENT marts_event_ticketing; line in your script is not attached to any load statements and does nothing. You can/have to remove it.

[Gabbar]

as@LRuCelver  said  , concatenate function is written like concatenate(Table_Name),and also you are dropping 'marts_event_ticketing' in the end without doing anything which means this script part is doing nothing.

[anderseriksson]

It is the concatenate statement at the top of the second load that does the work.
Your label directly after the concatenate statement does nothing.
The resident statement in the end is not needed, the drop is not needed.
Or rather if you drop the concatenated table at the end you will end up with nothing!
And the concatenate statement I prefer explisitly stating to what I want to concatenet by writing
concatenate(marts_event_ticketing)

[LRuCelver]

I also just noticed that the FROM statement from the 2nd load doesn't reference a file:

FROM "processed_production_marts"."marts_event_ticketing";

[Nicolae_Alecu · Accepted solution]

Hello,

I think your first table is loaded from .xlsx file and your second table is loaded from DB. 

In this case your script should look like : 

marts_event_ticketing: //Load first table
Load * //Your Fields
From [lib://DataFiles/Ticketing 2024.xlsx]
(ooxml, embedded labels, table is [Hoja 3]);

LIB CONNECT TO 'Your connection to database';  //Connect to databse

Concatenate(marts_event_ticketing) //Concatenate second table to the first one
SQL 
Select * //Your Columns
From "processed_production_marts"."marts_event_ticketing";


And you shouldn't drop any table.

[sersal10]

Hi, this works. It comes form a database in the cloud. It is already connected to the DB.