topic Re: country maping in QlikView

country maping

soniasweety — Wed, 25 Nov 2020 16:16:04 GMT

Hi all,

omar bensalem

i want to map the country values to limited. how can achieve this?

i have region and country

region:

Americas

AsiaPacific

EMEA

in country i have so many but my requirment is i want to show in country list if i select EMEA ,Americas,AsiaPacific , only below criteria should come.

In americas - UnitedStates,Canada ,OtherAMS

in AsiaPacific- India,China, OtherAPJ

in EMEA - Germany,UnitecdKingdom ,OtherEMEA

Re: country maping

YoussefBelloum — Mon, 10 Sep 2018 09:52:41 GMT

Hi,

you can try to use a where clause on the Country field, like this

table:

load

FROM

WHERE Match(Country,'UnitedStates','Canada ','OtherAMS','India','China','OtherAPJ','Germany','UnitecdKingdom ','OtherEMEA');

Re: country maping

soniasweety — Mon, 10 Sep 2018 10:11:08 GMT

Not working sir.

youssef belloum

in country listbox rest of the country's is not showing like

OtherAMS

OtherAPJ

OtherEMEA

Re: country maping

soniasweety — Mon, 10 Sep 2018 10:12:06 GMT

Hi Avinash R

Tresesco B any idea on this?

Re: country maping

Frank_Hartmann — Mon, 10 Sep 2018 10:25:56 GMT

Maybe like attached

hope this helps

Re: country maping

soniasweety — Mon, 10 Sep 2018 10:29:53 GMT

is there any other way in scripting

i dont want to use caluclated dimension we need to use direct dimension itself

Re: country maping

soniasweety — Mon, 10 Sep 2018 10:34:31 GMT

cant we achieve throguh this where clause?

FROM

WHERE Match(Country,'UnitedStates','Canada ','OtherAMS','India','China','OtherAPJ','Germany','UnitecdKingdom ','OtherEMEA');

Re: country maping

avinashelite — Mon, 10 Sep 2018 10:38:31 GMT

Create an new Country field like this

IF(Region='Asia Pacific' and (Country='India' or Country='China' ),Country,

if(Region='Asia Pacific' and (Country<>'India' or Country<>'China' ),'OtherAPJ',

if(Region='Americas' and (Country='United States' or Country='Canada' ),Country,

if(Region='Americas' and (Country<>'United States' or Country<>'Canada' ),'OtherAMS',

if(Region='EMEA' and (Country='Germany' or Country='United Kingdom' ),Country,

if(Region='EMEA' and (Country<>'Germany' or Country<>'United Kingdom' ),'OtherEMEA',Country

))

)))) as Country_NEW,

Re: country maping

YoussefBelloum — Mon, 10 Sep 2018 10:38:54 GMT

so you want to limit Country values only on selections ?

Re: country maping

soniasweety — Mon, 10 Sep 2018 10:40:43 GMT

i want to use country in pivot table when user select region the respected country values should show in pivot table.

Re: country maping

avinashelite — Mon, 10 Sep 2018 10:40:51 GMT

check the attachment

Re: country maping

OmarBenSalem — Mon, 10 Sep 2018 10:42:39 GMT

in ur load, try as follow:

if(Region='Americas', if(Country<>'UnitedStates' or Country<>'Canada', 'OtherAMS','Country),

if(Region='AsiaPacific', if(Country<>'India' or Country<>'China', 'OtherAPJ','Country),

if(Region='EMEA ', if(Country<>'Germany' or Country<>'UnitecdKingdom ', 'OtherEMEA','Country) )) as NewCountry

then use this new dimension

Re: country maping

soniasweety — Mon, 10 Sep 2018 10:47:48 GMT

syntax error it is showing

Re: country maping

OmarBenSalem — Mon, 10 Sep 2018 10:48:40 GMT

Just add anoter ) in the end

Re: country maping

soniasweety — Mon, 10 Sep 2018 10:52:10 GMT

No sir still same error

Re: country maping

vikasmahajan — Mon, 10 Sep 2018 10:59:08 GMT

Instead of multiple if it is recommended that you can use apply map command in Qlikview. Refer attached demo.

HTH

Vikas

Re: country maping

OmarBenSalem — Mon, 10 Sep 2018 11:00:50 GMT

My bad !

if(Region='Americas', if(Country<>'United States' and Country<>'Canada', 'OtherAMS',Country),

if(Region='Asia Pacific', if(Country<>'India' and Country<>'China', 'OtherAPJ',Country),

if(Region='EMEA', if(Country<>'Germany' and Country<>'United Kingdom', 'OtherEMEA',Country) ))) as NewCountry,

Result:

Re: country maping

Frank_Hartmann — Mon, 10 Sep 2018 11:58:55 GMT

see attached.

hope this helps

Re: country maping

soniasweety — Mon, 10 Sep 2018 12:44:47 GMT

Nice approach. Thanks