topic UnRelated fields or subfield in QlikView

UnRelated fields or subfield

Anonymous — Tue, 07 Oct 2014 08:06:21 GMT

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

Re: UnRelated fields or subfield

stevedark — Tue, 07 Oct 2014 10:56:34 GMT

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

Re: UnRelated fields or subfield

JonnyPoole — Tue, 07 Oct 2014 15:00:48 GMT

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

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