topic Re: Need Help in QlikView

Need Help

Thu, 09 Oct 2014 11:50:39 GMT

Hi All,

I have one query that I have one excel sheet USA

OrderID	LineNO	FormulaID	Country
1	200	AXY76	UNITED STATE A
2	300	BHC98	U.S.A
3	500	MM55	U.STATE.A
4	600	OI11	AMERICA
5	400	FR56	US

so I want in all country name as USA

Thanks and Regards,

Re: Need Help

Anonymous — Thu, 09 Oct 2014 12:03:08 GMT

Hello

Take a look on the attached file.

Regards,

Gabriel

Re: Need Help

Thu, 09 Oct 2014 12:07:55 GMT

if(match(Country,'UNITED STATE A','U.S.A','U.STATE.A','AMERICA','US'),'USA',Country) as Country,

Re: Need Help

ali_hijazi — Thu, 09 Oct 2014 12:15:23 GMT

you can use a mapping table and ApplyMap or in the script use in the script if(wildmatch(country,'united states','u.s.a.',...)>0,'USA',country)

Re: Need Help

Thu, 09 Oct 2014 12:38:59 GMT

Hi,

Maintain one more excel which will contain the countryname ex :

Country	CountryName
UNITED STATE A	USA
U.S.A	USA
U.STATE.A	USA
AMERICA	USA
US	USA

then use below script:

tempcountry:

LOAD

OrderID,

LineNO,

FormulaID,

Country

FROM

[..\..\Calendar.xlsx]

(ooxml, embedded labels, table is Sheet2);

maptable:

MAPPING LOAD

Country,

CountryName

FROM

[..\..\Calendar.xlsx]

(ooxml, embedded labels, table is Sheet3);

Country:

LOAD

OrderID,

LineNO,

FormulaID,

ApplyMap('maptable',Country) as countryname

Resident tempcountry;

DROP Table tempcountry;

hope it helps

Regards,

Pooja

Re: Need Help

ramasaisaksoft — Thu, 09 Oct 2014 12:50:24 GMT

Hi Prashat,

use this calculated dimension(Expression)

=if(match(Country,'UNITEDSTATEOFAMERICA','U.S.A','AMERICA','U.STATE.A','US'),'USA',Country)

Here country is u r field name

'UNITEDSTATEOFAMERICA','U.S.A','AMERICA','U.STATE.A','US these are u r data in country filed.

i hope u understand otherwise please check the files.

Re: Need Help

antoniotiman — Thu, 09 Oct 2014 12:50:31 GMT

Hi PM,

in Your Table You have only Field Country like U.S.A., America,.. or other Country ?

Re: Need Help

Thu, 09 Oct 2014 13:26:31 GMT

Thanks to all of you .

I will implement in my script and let you know......