Help

[panipat1990]

Hi,

   I am New In qlikview.....Please Help Me..

Suppose In Table one Field Contain Like Country  US,U.S.A,AMERICA,USA......IWant to all Field Value In One Format Like USA.......So Help Me Which Function I used.....Please Send Me QVW File.....Suppose in Database 2500 records not clear.....then how to resolve this issue In QlikView......please help me

[Anonymous]

I would use an inline table or an Excel file with all distinct values of your countries and the wanted outcome

define it as mappping table and convert your Countryfield according

short example with inline

CountryConvert:

mapping load * Inline [

Country, CountryShort

US, USA

U.S.A, USA

AMERICA, USA

USA, USA

];

in your fact table:

FACTTABLE:

Load

field1,

field2,

applymap('CountryConvert', Country) as NewCountryShort,

Field3

...

If you have a lot of distinct values it is an awesome one time job

[robert_mika]

You will have to create mapping load table

One fields with your Country and another one with the correct return:

Country,ProperCountry

US,USA

U.S.A,USA

AMERICA,USA

USA,USA

Here is tutorial how to do it

Don't join - use Applymap instead

or you can use this solution

Missing Manual - Map...using

[sasiparupudi1]

Use Mapping load

See attached

MAP:

mapping LOAD * Inline

[

From,To

US,USA

U.S.A,USA

AMERICA,USA

IND,INDIA

CHN,CHINA

];

Data:

LOAD ApplyMap('MAP',Country,'NA') as Country,Count

Inline

[

Country, Count

U.S.A,100

IND,200

US,400

CHN,9000

];

[#5]

Hi,

use apply map.

Country:

Mapping

Load * inline[

Country,CountrySame

US,USA

United State, USA

America, USA

];

Maintable:

LOAD *,

applymap('Country',field,'NA') as Country

from table;

Regards

Vimlesh

[marcus_malinow]

I see you've already asked this question, and received some decent answers.

Help

Would suggest you show some courtesy to those who have already answered your question by clarifying why their answers don't meet your requirements.

[sunny_talwar]

If you don't want to use Mapping Load, you can fix this with a if statement also:

If(Match(Country, 'US', 'U.S.A', 'AMERICA', 'USA'), 'USA', Country) as Country

[kuczynska · Accepted solution]

You can always try to use function match() and define all of the possible versions of names for each country. You will have to nest IF statements in your load script, if you want to re-map more than one country. If no match has been found, word MISSING will be displayed (you can of course change it to anything you like):

IF(match(Country, 'US', 'United States', 'America' , 'U.S.A', 'USA', 'AMERICA') , 'USA',

    IF(match(Country, 'CHN', 'China'), 'CHINA',

          IF(match(Country, 'IND', 'India'), 'INDIA', 'MISSING'

          )

    )

) AS CountrySame

But of course - solutions with mapping loads will work for this task too.