How to connect 2 tables using text in a field

[Mr_Pearl]

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.

[Lisa_P · Accepted solution]

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);