Qlik Community

Ask a Question

App Development

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

Showing results for 
Search instead for 
Did you mean: 
Contributor II
Contributor II

Need to join two table for creating a derived_field

Need to join two tables for creating a derived field

Hello Team,

 I am trying to joining two tables, but there is no primary key in between them, please find the below scenario and suggest any possible solution.


SalesOrderIDProductNameOrder ReferenceCountry
452832nd Check Opens Cons61145United Arab Emirates Egypt Iraq Iran, Islamic Republic of Jordan Kuwait Lebanon Libya Oman Qatar Saudi Arabia Yemen
46042Checking Funded SB6157United Arab Emirates Egypt Iraq Iran
46976Savings Open Cons61892Canada Saint Pierre and Miquelon United States
47997Savings Funded Cons61953Canada Saint Pierre and Miquelon United States United Arab Emirates Egypt



United Arab EmiratesMiddle east
CanadaNorth America
EgyptMiddle east
IraqMiddle east
Iran, Islamic Republic ofMiddle east
JordanMiddle east
KuwaitMiddle east
LebanonMiddle east
LibyaMiddle east
OmanMiddle east
Saint Pierre and MiquelonNorth America
QatarMiddle east
Saudi ArabiaMiddle east
United StatesNorth America
YemenMiddle east


I need to compare country field in dataset 1 and country field in dataset2 and create new field with region value.


output would be :

SalesOrderIDProductNameOrder Referenceregion
452832nd Check Opens Cons61145Middle east
46042Checking Funded SB6157Middle east
46976Savings Open Cons61892North America
47997Savings Funded Cons61953North America,Middle east


Can anyone help me with the possibilities to achieve the same.


Thanks in advance..

Labels (1)
2 Replies

You can join all rows of both tables and then consolidate to a third table using the wildmatch() function and grouping the fields.

Once you load the 2 data sets, add the following load script:

Join (DataSet1)
Load Country as Country2,
Resident DataSet2;

Load SalesOrderID,
[Order Reference],
Concat(DISTINCT Region,',') as Region
Resident DataSet1
where WildMatch([Country],'*'&Country2&'*') > 0
Group by SalesOrderID, ProductName, [Order Reference];

drop tables DataSet1, DataSet2;

MVP & Luminary
MVP & Luminary

If your country-field from dataset 1 contained a valid delimiter between the multiple entries you could split them into multiple records, for example with:

subfield(Country, Delimiter) as Country

and on it you could simply join/map the dataset 2 against it.

If there is not such delimiter like it looked in your example because the country-items itself contain spaces and the delimiter seems to be a space it's not working.

In this case you could load the dataset 2 at first within a mapping-table and using mapsubstring() to replace the entries with the regions and/or you replaced them with their own value - but adding a valid delimiter to them. And then you could apply subfield() again like described above.

- Marcus