topic Re: Need to join two table for creating a derived_field in App Development

Need to join two table for creating a derived_field

Correct_Answer — Tue, 08 Jun 2021 09:55:20 GMT

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

SalesOrderID	ProductName	Order Reference	Country
45283	2nd Check Opens Cons	61145	United Arab Emirates Egypt Iraq Iran, Islamic Republic of Jordan Kuwait Lebanon Libya Oman Qatar Saudi Arabia Yemen
46042	Checking Funded SB	6157	United Arab Emirates Egypt Iraq Iran
46976	Savings Open Cons	61892	Canada Saint Pierre and Miquelon United States
47997	Savings Funded Cons	61953	Canada Saint Pierre and Miquelon United States United Arab Emirates Egypt

DataSet2

Country	Region
United Arab Emirates	Middle east
Canada	North America
Egypt	Middle east
Iraq	Middle east
Iran, Islamic Republic of	Middle east
Jordan	Middle east
Kuwait	Middle east
Lebanon	Middle east
Libya	Middle east
Oman	Middle east
Saint Pierre and Miquelon	North America
Qatar	Middle east
Saudi Arabia	Middle east
United States	North America
Yemen	Middle 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 :

SalesOrderID	ProductName	Order Reference	region
45283	2nd Check Opens Cons	61145	Middle east
46042	Checking Funded SB	6157	Middle east
46976	Savings Open Cons	61892	North America
47997	Savings Funded Cons	61953	North America,Middle east

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

Thanks in advance..

Re: Need to join two table for creating a derived_field

GaryGiles — Tue, 08 Jun 2021 14:36:29 GMT

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;

Re: Need to join two table for creating a derived_field

marcus_sommer — Wed, 09 Jun 2021 09:38:03 GMT

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