Qlik Community

Ask a Question

App Development

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

cancel
Showing results for 
Search instead for 
Did you mean: 
Correct_Answer
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.

DataSet1 

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

 

DataSet2

CountryRegion 
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
GaryGiles
Specialist
Specialist

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,
Region
Resident DataSet2;

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

drop tables DataSet1, DataSet2;

marcus_sommer
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