Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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..
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;
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