Qlik Community

New to Qlik Sense

If you’re new to Qlik Sense, start with this Discussion Board and get up-to-speed quickly.

Announcements
QlikWorld 2022, LIVE in Denver CO., May 16-19, 2022. REGISTER NOW TO RECEIVE EARLY BIRD PRICING
cancel
Showing results for 
Search instead for 
Did you mean: 
Mr_Pearl
Creator
Creator

How to connect 2 tables using text in a field

I got 2 tables (Please see attached file). I have to connect this 2 tables in Qlik sense data load editor so that I can create viz in app. I would like to connect this 2 tables using the only common field that has common data which are "rule" field in "Table1.xlsx" and "GroupName" field in "Table2.xlsx".

I am blank because that data presented is in substring. Can you please give me idea of how I can connect this 2 table.

Note: Data are dynamic everyday. For example there can be more "GroupName" in Table 2 the next day when I load the dashboard and subsequently there can be more or less "GroupName" assigned against a fruit in Table1.

Any help will be much appreciated.

Labels (3)
1 Solution

Accepted Solutions
Lisa_P
Employee
Employee

I just cleaned up the text using string functions to enable the subfield to work:

Load *,
SubField(
Replace(Replace(REplace(rule, '((user.group="', '') , ' or user.group="', ''), '"))', '')
, '"')
as GroupName;
LOAD
id,
Name,
rule
FROM [lib://Data/Table1.xlsx]
(ooxml, embedded labels, table is Table1);

LOAD
GroupName,
"Column1(Filled with Random data)"
FROM [lib://Data/Table2.xlsx]
(ooxml, embedded labels, table is Table2);

 

View solution in original post

1 Reply
Lisa_P
Employee
Employee

I just cleaned up the text using string functions to enable the subfield to work:

Load *,
SubField(
Replace(Replace(REplace(rule, '((user.group="', '') , ' or user.group="', ''), '"))', '')
, '"')
as GroupName;
LOAD
id,
Name,
rule
FROM [lib://Data/Table1.xlsx]
(ooxml, embedded labels, table is Table1);

LOAD
GroupName,
"Column1(Filled with Random data)"
FROM [lib://Data/Table2.xlsx]
(ooxml, embedded labels, table is Table2);

 

View solution in original post