Skip to main content

App Development

Discussion board where members can learn more about Qlik Sense App Development and Usage.

Announcements
QlikWorld 2023, a live, in-person thrill ride. Save $300 before February 6: REGISTER NOW!
cancel
Showing results for 
Search instead for 
Did you mean: 
galafish
Contributor II
Contributor II

Create table in Data Load Editor based on previously loaded tables

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_idcontent_tag
1Analytics
2Analytics;Big Data
3Big Data;Compliance
4Analytics;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_idcontent_tagcontent_id
1AnalyticsAnalytics
2Analytics;Big DataAnalytics
2Analytics;Big DataBig Data
3Big Data;ComplianceBig Data
3Big Data;ComplianceCompliance
4Analytics;Compliance;Big DataAnalytics
4Analytics;Compliance;Big DataCompliance
4Analytics;Compliance;Big DataBig 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!

Labels (2)
1 Solution

Accepted Solutions
zzyjordan
Creator II
Creator II

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:

Untitled.jpg

Hope this helps

ZZ

View solution in original post

5 Replies
zzyjordan
Creator II
Creator II

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:

Untitled.jpg

Hope this helps

ZZ

jonathandienst
Partner - Champion III
Partner - Champion III

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
 

 

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein
jonathandienst
Partner - Champion III
Partner - Champion III

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

 

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

 

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein
galafish
Contributor II
Contributor II
Author

Thank you so much! It worked!

Nils9
Contributor II
Contributor II

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