topic Re: Switch…case to merge different countries in QlikView

Switch…case to merge different countries

terezagr — Wed, 22 Jan 2014 11:30:25 GMT

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!

Re: Switch…case to merge different countries

hic — Wed, 22 Jan 2014 11:45:16 GMT

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

Re: Switch…case to merge different countries

tresB — Wed, 22 Jan 2014 11:46:22 GMT

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.

Re: Switch…case to merge different countries

terezagr — Wed, 22 Jan 2014 14:47:31 GMT

Hi,

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

Thank you!

Re: Switch…case to merge different countries

Wed, 22 Jan 2014 16:26:25 GMT

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