UnRelated fields or subfield

[Anonymous]

Hi,

I am using sub -field to relate the data now I need to remove the unwanted or unrelated data.

I am attaching the app do let me know the suggestions

[stevedark]

You are getting some rogue joins from where you are splitting the country name out.  AND for example exists in a number of country names and is therefore joining erroneously.

It would appear to me that you need to build a mapping table between PRFT_CTR_LVL_2_CD and BUYER_COUNTRY, eg:

Map_LVL2Country:

MAPPING LOAD

     Code,

     Name

INLINE [

Code,Name

PCG00903,US

... rest of mappings here ...

];

And then map this onto the data, like this:

ApplyMap('Map_LVL2Country', PRFT_CTR_LVL_2_CD. 'UNKNOWN') as BUYER_COUNTRY,

It then looks like some Level 2 codes relate to more than one country, so you may need to map some codes at level 6 instead, you can do this by nesting applymaps:

ApplyMap('Map_LVL2Country', PRFT_CTR_LVL_2_CD,

ApplyMap('Map_LVL6Country', PRFT_CTR_LVL_6_CD, 'UNKNOWN') as BUYER_COUNTRY,

You can then map things at different levels depending on the granularity of the two data sources.  It will be a bit manual to set up the lookup lists, but I'm not sure it will work any other way.

My blog post on ApplyMap goes into this in quite some detail:  http://bit.ly/kQcAZ5

Hope that helps,

Steve

[JonnyPoole]

Same issue for all 3 cases in the QVW,  you are joining on word components of the country name that are shared amongst unrelated countries.  Best to filter out those words from join... 'of' , 'and' , 'republic'.  Probably also 'north','south' etc.... there could be many.

Here is a quick and dirty way to filter a loaded table in this way. Longer lists of words should be compiled in lookup table and filtered out through a table join in the script or a mapping load per Steve's writeups above.

Temp:

LOAD

  CountryName,

  subfield(purgechar(Upper(Name),','),' ') as CountryWord

FROM

(ooxml, no labels, table is Sheet1)

;

NoConcatenate

Countries:

Load

  *

resident Temp

where

  CountryWord<>'AND' and

  CountryWord<>'OR' and

  CountryWord<>'REPUBLIC' and

  CountryWord<>'OF' and

  CountryWord<>'PEOPLES'

;

drop table Temp;

-----------------------------

Q/A from QVW:

1) Here if we select the BOSNIA and Herzegovin we should only get Bosia Sales

     -  since you are joining on word,  'and'  will be used to join to other countries with 'and' in the name like 'Trinidad' and 'Tobago' etc... 

2) if we select China we are getting Central Africa ,Republic of Congo

     - the selection is actually 'China, Peoples Republic of' . and if you join on any one of these  words , then you are going to join on 'of' and 'Republic'  which will cause a hit on 'Central Africa Republic' and 'Republic of Congo'.

3)if we select Korea Republic we are getting Bank of America, Centarl African and Republic of Congo

     -  the selection is actually 'Korea, Republic of'  ...same issue as #2

---------------------------------