Qlik Community

Qlik Sense App Development

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

Announcements
QlikWorld 2020: Join us May 11 - 14, 2020 in Phoenix, AZ. Register early and save $400. Learn More
galafish
New 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
Contributor II

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

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

4 Replies
zzyjordan
Contributor II

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

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

MVP
MVP

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

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
MVP
MVP

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

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
New Contributor II

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

Thank you so much! It worked!