topic Re: Create table in Data Load Editor based on previously loaded tables in App Development

Create table in Data Load Editor based on previously loaded tables

galafish — Sat, 16 Nov 2024 21:35:50 GMT

Hi All, newbie's here!

I have two previously loaded tables in Load Editor:

table1 is [ContentTagList] with one column content_id, which stores all content tags, one for each record like this:

content_id

Analytics

Big Data

Compliance

table2 is [EventContentTag] with two columns event_id and content_tag, which can include single or multiple content_id, divided by ';' per event_id, like this:

event_id	content_tag
1	Analytics
2	Analytics;Big Data
3	Big Data;Compliance
4	Analytics;Compliance;Big Data

Now I need to create third table based on first two with three columns: event_id, content_tag, content_id like this:

event_id	content_tag	content_id
1	Analytics	Analytics
2	Analytics;Big Data	Analytics
2	Analytics;Big Data	Big Data
3	Big Data;Compliance	Big Data
3	Big Data;Compliance	Compliance
4	Analytics;Compliance;Big Data	Analytics
4	Analytics;Compliance;Big Data	Compliance
4	Analytics;Compliance;Big Data	Big Data

After this I will be able to filter by content_id and get the list of all event_id that have this content tag.

If there is other way to achieve this, please advise.

Thank you!

Re: Create table in Data Load Editor based on previously loaded tables

zzyjordan — Wed, 16 Jan 2019 01:53:40 GMT

Hi, there

Try similar script as below (I load the two tables from excel, you need change it to your own)

tmp_data:
LOAD
content_id
FROM [lib://AttachedFiles/0116.xlsx]
(ooxml, embedded labels, table is Content);

Inner Join(tmp_data)
LOAD
event_id,
content_tag
FROM [lib://AttachedFiles/0116.xlsx]
(ooxml, embedded labels, table is event);

NoConcatenate
Final_Data:
load *
Resident tmp_data
Where SubStringCount(content_tag,content_id)>0;

Drop Table tmp_data;

And result looks like this:

Hope this helps

Re: Create table in Data Load Editor based on previously loaded tables

jonathandienst — Wed, 16 Jan 2019 06:42:43 GMT

Use the two parameter version of SubField():

LOAD 
	event_id,
	content_tag,
	SubField(content_tag, ';') as content_id
Inline
[
	event_id,content_tag
	1,Analytics
	2,Analytics;Big Data
	3,Big Data;Compliance
	4,Analytics;Compliance;Big Data

Re: Create table in Data Load Editor based on previously loaded tables

jonathandienst — Wed, 16 Jan 2019 06:45:56 GMT

To load from a previously loaded table, for example DataTable:

LOAD event_id,
    content_tag,
    SubField(content_tag, ';') as content_id
Resident DataTable;

Re: Create table in Data Load Editor based on previously loaded tables

galafish — Wed, 16 Jan 2019 17:31:02 GMT

Thank you so much! It worked!

Re: Create table in Data Load Editor based on previously loaded tables

Nils9 — Tue, 02 Aug 2022 10:51:05 GMT

This is exactly what I was looking for, thanks a ton!