topic Re: Loop to fetch the subregion from two different tables in QlikView

Loop to fetch the subregion from two different tables

Anonymous — Fri, 26 Sep 2014 10:28:27 GMT

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.

Re: Loop to fetch the subregion from two different tables

Anonymous — Fri, 26 Sep 2014 11:34:11 GMT

why don't you prepare it in the Excel file?

or you can define a mapping table in qlikview

(lots of work I guess)

as I see there are not many rows which might fit

between the tables (e.g. Jamaica-> where is the

Connection between Subreg and Main?)

Re: Loop to fetch the subregion from two different tables

Anonymous — Fri, 26 Sep 2014 11:46:15 GMT

Rudolf thanks for reply

those are from DB for convenience purpose i taken into Excel.

the common column is CountryName and BR_Country because the values are same but in CountryName the values may be a bit extended

Example:

Country Name in BR_Country is America the the countryName in CountryName is AmericaSales need to compare these two to get the SubRegion of America

Re: Loop to fetch the subregion from two different tables

Anonymous — Fri, 26 Sep 2014 11:54:44 GMT

Hi,

Something is missing between your data(Tables) please check the data once and post the sample data again...........

Re: Loop to fetch the subregion from two different tables

Anonymous — Fri, 26 Sep 2014 11:58:51 GMT

thanks for reply Sreekanth ,

everything is correct

1) load the excel in QV and rename the CountryName2 as BuyerCountry and reload

2) take a tablebox and select all the fields and now go to the excel sheet select the Buyercountry and cross check in the Subregion you will get an idea

Re: Re: Loop to fetch the subregion from two different tables

Anonymous — Fri, 26 Sep 2014 12:02:39 GMT

Hi,

Pleas find the attachments...

Re: Loop to fetch the subregion from two different tables

Anonymous — Fri, 26 Sep 2014 12:11:18 GMT

Hi,

it doesnt yielded the result

as you select any country even though the values are not showing in Subregion.

Re: Loop to fetch the subregion from two different tables

afurtado — Sun, 28 Sep 2014 03:59:33 GMT

Hi,

I tried to help, but like Venkata Sreekanth told, maybe is missing some column.....

SubRegion	CountryName2
Americas	Americas
Asia Pacific	Asia Pacific
Brazil DC	Latin America

BR_Country

BUYER_COUNTRY

UNKNOWN

UNITED ARAB EMIRATES

GHANA

Best,

Alessandro Furtado

Re: Loop to fetch the subregion from two different tables

Anonymous — Sun, 28 Sep 2014 15:42:58 GMT

thanks for the reply but as in the above mentioned table in your solution area

SubRegion	CountryName2
Americas	Americas
Asia Pacific	Asia Pacific
Brazil DC	Latin America

BR_Country

BUYER_COUNTRY

UNKNOWN

UNITED ARAB EMIRATES

GHANA

Here, we need to get the SubRegion from mapping of BR_Country and CountryName2. THe final resultig table should be with 2 columns BRCountry and SUbRegion.

i.e if we select GHANA it needs to do check with the CountryName2 table for GHANA and then it needs to fetch the SunRegion.

the data that i given is correct as per the requirment

Hope u get the right inputs

Re: Loop to fetch the subregion from two different tables

Anonymous — Mon, 29 Sep 2014 03:19:26 GMT

bump

Re: Re: Loop to fetch the subregion from two different tables

JonnyPoole — Mon, 29 Sep 2014 14:13:24 GMT

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.