topic Re: Help in QlikView

Help

panipat1990 — Thu, 30 Jul 2015 09:41:52 GMT

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

Re: Help

Anonymous — Thu, 30 Jul 2015 09:48:45 GMT

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

Re: Help

robert_mika — Thu, 30 Jul 2015 09:49:09 GMT

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

Re: Help

sasiparupudi1 — Thu, 30 Jul 2015 09:51:34 GMT

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

];

Re: Help

Thu, 30 Jul 2015 10:25:44 GMT

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

Re: Help

marcus_malinow — Thu, 30 Jul 2015 10:29:29 GMT

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.

Re: Help

sunny_talwar — Thu, 30 Jul 2015 10:29:41 GMT

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

Re: Help

kuczynska — Thu, 30 Jul 2015 10:37:59 GMT

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.