Create table in Data Load Editor based on previously loaded tables

[galafish]

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!

[zzyjordan · Accepted solution]

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

ZZ

[jonathandienst]

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
 

[jonathandienst]

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

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

[galafish]

Thank you so much! It worked!

[Nils9]

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