Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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
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
It's a bad idea to not fix this in the script, but if you insist ...see attached example.
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))
although i hv mentioned "not to rewrite script"
the first reply seems to be the easiest to implement
and thanks to all!