topic create a new dimension in QlikView

create a new dimension

laujerry — Mon, 15 Jul 2013 08:37:44 GMT

let say i have sales amount (fact) for different countries

and i want to group the countries accordingly in a pivot table

original:

country1	10
country2	15
country3	12
country4	20
country5	17
country6	20

result:

Eastern Europe	country1	10
	country2	15
Western Europe	country3	12
	country4	20
Europe	country1	10
	country2	15
	country3	12
	country4	20
North America	country5	17
South America	country6	20
Americas	country5	17
	country6	20

i have found sth like Aggr which may be useful: http://community.qlik.com/message/125490#125490

but in my case a country may fall into more than 1 self-defined dimension

and using the if-else flow will not work then

so how can i solve the problem (besides rewriting scripts!)? thanks

Re: create a new dimension

kaushiknsolanki — Mon, 15 Jul 2013 08:43:34 GMT

Hi,

You need to somewhere define that which country is under what.

As I can see in your expected output that you need the Europe and Americas.

So you should create a inline table which will be something like

Load * inline [

Zone,Region,Country

Americas,North,Country5

Americas,South,Country6

Europe,Eastern,Country1

Europe,Eastern,Country2

Europe,Western,Country3

Europe,Western,Country4

];

This table should link with the Country field to make the relationship.

Then you can use the Zone and Region field in your chart.

Regards,

Kaushik Solanki

Re: create a new dimension

Gysbert_Wassenaar — Mon, 15 Jul 2013 08:47:02 GMT

It's a bad idea to not fix this in the script, but if you insist ...see attached example.

Re: create a new dimension

brijesh1991 — Mon, 15 Jul 2013 11:14:18 GMT

Use following expression:

pick

(match(ValueList('Eastern Europe','Western Europe','Europe','North America','South America','Americas'),'Eastern Europe','Western Europe','Europe','North America','South America','Americas')

,sum({<Country={'country1','country2'}>}Value)
,sum({<Country={'country3','country4'}>}Value)
,sum({<Country={'country1','country2','country3','country4'}>}Value)
,sum({<Country={'country5'}>}Value)
,sum({<Country={'country6'}>}Value)
,sum({<Country={'country5','country6'}>}Value))

Re: create a new dimension

laujerry — Tue, 16 Jul 2013 01:29:26 GMT

although i hv mentioned "not to rewrite script"

the first reply seems to be the easiest to implement

and thanks to all!