create a new dimension

[laujerry]

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

[kaushiknsolanki · Accepted solution]

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

[Gysbert_Wassenaar]

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

[brijesh1991]

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))

[laujerry]

although i hv mentioned "not to rewrite script"

the first reply seems to be the easiest to implement

and thanks to all!