Switch…case to merge different countries

[terezagr]

Hi all,

I have table that looks like this

Country                  Sales

England                    10

Scotland                   20

Wales                       30

EU                            20

Northern Ireland         10

What I am trying to achieve is this:

Country      Sales

Scotland        20

EU                20

UK                50                        

I have written CASE statement as this, but no success.

(Case

where Country='Scotland' as 'Scotland'

where Country='EU' as 'EU'

where (Country='England' and Country='Wales' and Country='Northern Ireland') as 'UK'

else 'Other'

end

)

Any thoughts how I could achieve this?

Thanks for your help in advance!

T.

[hic]

I would use ApplyMap, e.g.

CountryMap:

Mapping Load * inline

[Source, Target

England, UK

Wales, UK

Northern Ireland, UK];

If you now load your data using

  Applymap('CountryMap',Country) as Country

it will work straight off.

HIC

[tresesco]

Why not simply like:

Load

          If( Match(Country,'England','Wales', 'Northern Ireland'), 'UK', Country) as Country,

          Sales

From <>;

This will give you:

Country             Sales

UK                       10

Scotland              20

UK                       30

EU                        20

UK                        10

Now in the front end if you use a chart you will get your desired result. And the same can be done using mapping as well.

[terezagr · Accepted solution]

Hi,

eventually solved it by using  tresesco approach applied as calculated dimension.

Thank you!

[#5]

Please mark the answer as correct, if you do not have any other questions. Thanks please