Loop to fetch the subregion from two different tables

[Anonymous]

Hi folks/ QvExperts,

I am new to Qv and stuck in complex situation.

I am sharing the sample excel sheet and my requirement is :

I have three differenct tables those are BR_Country, CountryName and SubRegion. Here i need to map the BR_country to the CountryName to get the SubRegion , i need to create a loop for searching of all the country names in BR_Country and to compare with the CountryName table to get the SubRegion

Resultant table should be with two columns BR_Country and SubRegion

Note: country names in the "CountryName " will be AmericaSales and in the "BR_Country" will be America but need to be map these two to fetch the Subregion.

Hope you understand the requirement.

[JonnyPoole · Accepted solution]

I created 2 join rules

1. 'As is' case insensitive join on upper([CountryName2]) =upper( [BR_Country])  -> this created some hits

2.  On whatever is left (use WHERE NOT EXISTS) , join where  any of the component words with the commas stripped match up:

          subfield(purgechar(upper(BR_Country),','), ' ') as CountryWord

          subfield( purgechar(upper(CountryName2),',') , ' ') as CountryWord

-> this allowed me to join say,  ' CHINA,PEOPLES REPUBLIC OF'  to 'CHINA' because the word CHINA exists in both.  (it also yielded Korea).  To do this i had to filter out 'SOUTH' because that would join SOUTH AFRICA to SOUTH PACIFIC for example.

The 3rd step is to table concatenate the 2 results together. Attached is the result so for .

What other rules would you want to join  ? 

As you build up more and more scenarios you will get more and more hits, the trick is to not join anything you've already joined and to combine the results with each successive wave.

Hopefully this helps you get started.