Can anyone help me to load the new data?

[durgabhavani]

Hi All,

I have countries like below in country field.

Country:

India

Australia

Srilanka

newzeland

canada

pakistan

united states

united kingdom

united arab emirates

new field need to added is region:

Region

Asia-pacfic

Middleeast

Europe

Africa

-> scenario is to load region and the countries related to that region need to fall under region.

-> Next my bar chart dimension needs to display the region and when click on particular region bar its needs to show the countries under that region.

-> After getting countries in bar char, i need to come up one level (region) if i want to come back.

Please help me in prepare the Barchart?

Please share the code or screenshot, Don't share .qvw files as i no access to download and upload .qvw files due to security reasons.

Thanks,

Durga

[sunny_talwar · Accepted solution]

You would have to manual attach each Country to a location (Can't think of a work around that)

Script:

Table:

LOAD * INLINE [

    Region, Country, Sales

    Asia-Pacific, India, 15000

    Asia-Pacific, Pakistan, 24000

    Asia-Pacific, Sri Lanka, 35000

    Asia-Pacific, Australia, 22000

    Asia-Pacific, New Zealand, 10000

    Middle East, UAE, 32000

    Europe, UK, 11000

    North America, USA, 21000

    North America, Canada, 32000

];

Create a drill down group with Region and Country like this (within document properties):

Then use the drill down group as your dimension and you should have this chart:

and once you click on a particular region, it will show you the countries within that region

and then to go back to your region click on the arrom (circled it on the bottom right of the chart)

I hope this is what you are looking for.

Best,

Sunny

[durgabhavani]

Sunny, I am looking for this exactly. Thanks a lot!

Thanks,

Durga

[sunny_talwar]

Awesome, I am glad I got it in the first go...

Best,

Sunny

[MK_QSL]

1) Use below data in your script

Sales:

Load

  Country,

  If(Match(Country,'India','Pakistan','Sri Lanka'),'Asia',

  If(Match(Country,'Australia','New Zealand'), 'Australia',

  If(Match(Country,'UK','Germany','France'),'Europe',

  If(Match(Country,'USA','Canada'), 'America',

  If(Match(Country,'UAE'),'MiddleEast',

  If(Match(Country,'South Africa','Kenya'), 'Africa')))))) as Region,

  Sales

Inline

[

  Country, Sales

  India,   120

  Australia, 140

  Sri Lanka,  160

  Pakistan,   75

  Canada,     80

  New Zealand,100

  UK,         90  

  USA, 65

  France, 125

  Germany, 175

  UAE, 110

  South Africa, 100

  Kenya, 115

];

2) Create a New Sheet Object.. >> Bar Chart

Go to Dimension Tab

Select Edit Groups >> New >> Give a Group Name i.e. Region-Country

Select Drill Down Group

First Select Region Add

2nd Select Country Add >> OK >> OK

3) Add Region-Country Group as Dimension

4) SUM(Sales) as Expression

Hope this would help !

[durgabhavani]

This is also really helpful. I will keep track of all these points.

Thanks, Manish