COUNTRY SEGGREGATION TO CREATE A GROUP

[Irshad]

I have a single column dimension called "Country Name" where i have 146 countries.

Out of these 146 countries i am willing to pick selective countries to create  & place then in their corresponding CLUSTERS(NEW DIMENSION) to where they will belongs to.

>Current formula in am trying to use is as below but is KO

=if(country name="A', 'CLUSTER A',  if (country name='B",'CLUSTER B','STANDALONE'))  - RESULT KO

=if(country name="A', 'CLUSTER A' OR if (country name='B",'CLUSTER B','STANDALONE'))  - RESULT KO

I really appreciate your help in how to be able to properly fix this workings so i can then filter countries per CLUSTER.

[Digvijay_Singh]

If you are creating new master dimension or chart dimension, may be this, looks like you are using double quote and single quote improperly - 

=if([country name]='A', 'CLUSTER A', if ([country name]='B','CLUSTER B','STANDALONE'))

or may be like this - 

Aggr(if(country name='A', 'CLUSTER A', if (country name='B','CLUSTER B','STANDALONE')),[country name])

if you are creating it in a script then - 

if(country name='A', 'CLUSTER A', if (country name='B','CLUSTER B','STANDALONE')) as Cluster,

Thanks,

[Irshad]

Dear Digvijay,

Namaste,

Not the desired result, anything i am doing wrong ?

I chose france & belgium for CLUSTER A, but showing only France + GErmany (dont know where i comes from.

[Digvijay_Singh]

Namaste!

Its confusing to me, you mentioned you chose France and Belgium for Cluster A, what do you mean by you chose, did you select France and Belgium in the country filter?

If you check the expression, you are adding Belgium in CLUSTER B so I don't understand what do you mean by you chose France and Belgium for Cluster A, so I suggest match your criteria with the expression. May be share the sample app and the expected results in the chart.

Thanks,

[Irshad]

Namaste Bhai Ji, sharing the files per attached,

Maaf for the trouble,

Shukriya for your help.

[Digvijay_Singh]

Thats a big list of counties 😀

May be something like this - 

Load *,
If(Match(Country,'Japan'),'FEA CLUSTER',
If(Match(Country,'China','Hong Kong SAR, China'),'CHINA CLUSTER',
If(Match(Country,'Reunion','Madagascar'),'IOI CLUSTER',
If(Match(Country,'Saudi Arabia','United Arab Emirates','Qatar'),'MEA CLUSTER',
If(Match(Country,'Algeria','Egypt','Morocco','Libya'),'NAF CLUSTER',
If(Match(Country,'Australia','Papua New Guinea'),'OCE CLUSTER',
If(Match(Country,'Angola','Senegal'),'WAF CLUSTER'))))))) as CLUSTER
;
LOAD
"Week",
"GEO ZONE",
Country

FROM [lib://Qlik/QLik.xlsx]
(ooxml, embedded labels, table is Sheet2);

[Digvijay_Singh · Accepted solution]

The better solution could be to have a mapping table like this - 

map_table:
mapping Load
* inline [
Country,CLUSTER
Japan, FEA CLUSTER
China, CHINA CLUSTER
"Hong Kong SAR, China", CHINA CLUSTER
Reunion, IOI CLUSTER
Madagascar, IOI CLUSTER
Saudi Arabia, MEA CLUSTER
United Arab Emirates, MEA CLUSTER
Qatar, MEA CLUSTER
Algeria, NAF CLUSTER
Egypt, NAF CLUSTER
Morocco, NAF CLUSTER
Libya, NAF CLUSTER
Australia, OCE CLUSTER
Papua New Guinea, OCE CLUSTER
Angola, WAF CLUSTER
Senegal, WAF CLUSTER
];

source:
Load *,
applymap('map_table',Country,'Unknown CLUSTER') as CLUSTER
;
LOAD
"Week",
"GEO ZONE",
Country

FROM [lib://Qlik/QLik.xlsx]
(ooxml, embedded labels, table is Sheet2);

[Irshad]

Load thanks Digvijay, its working fine now.