Skip to main content
Announcements
NEW: Seamless Public Data Sharing with Qlik's New Anonymous Access Capability: TELL ME MORE!
cancel
Showing results for 
Search instead for 
Did you mean: 
sersal10
Creator
Creator

How to concatenate 2 tables in the load editor

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

Labels (3)
1 Solution

Accepted Solutions
PrashantSangle

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

 

Great dreamer's dreams never fulfilled, they are always transcended.
Please appreciate our Qlik community members by giving Kudos for sharing their time for your query. If your query is answered, please mark the topic as resolved 🙂

View solution in original post

12 Replies
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

Great dreamer's dreams never fulfilled, they are always transcended.
Please appreciate our Qlik community members by giving Kudos for sharing their time for your query. If your query is answered, please mark the topic as resolved 🙂
PhanThanhSon
Creator II
Creator II

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
Creator
Creator
Author

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
Creator
Creator
Author

Hi, 

It seems it doesn't work.

I would need something like a UNION ALL in SQL.


Cheers

 

Captura de pantalla 2024-03-19 a las 10.46.16.png

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

Great dreamer's dreams never fulfilled, they are always transcended.
Please appreciate our Qlik community members by giving Kudos for sharing their time for your query. If your query is answered, please mark the topic as resolved 🙂
sersal10
Creator
Creator
Author

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

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

 

Great dreamer's dreams never fulfilled, they are always transcended.
Please appreciate our Qlik community members by giving Kudos for sharing their time for your query. If your query is answered, please mark the topic as resolved 🙂
sersal10
Creator
Creator
Author

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

Cheers

sersal10
Creator
Creator
Author

Hi

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