Google Chart API Map

[#1]

I'm hoping that someone may be able to help me with this?

I've taken a previous example from Robs Cookbook where he had integrated QlikView with the old Google Chart API as i was interested to see if i could make use of the county data that the new map chart allows you to gain access to for the UK.

So far this is what i have achieved.

My problem is that although i have most of it working i'm struggling to get the text boxes for each county in the right place.

What i'm trying to achieve in the line below that i have got in my expression that is calling the API is to pass in a string with all the text information concatenated together. Now i have got it doing that but the problem i have got is that the text string must pass each text string in with a sequential number for how it is loading each row starting from '0' .

I'm trying to use fieldIndex('State', State) but that is not really doing what i want it to. The sequence numbers i have got so far are 1,2,3,4,5,6,7 so basically all my text boxes are once county off based on the data values being passed in

& '&chm=' & concat(DISTINCT 'fGB-' & State & ' Sales:' & Sales & ',000000,0,' & fieldIndex('State', State) & ',15', '|', fieldIndex('State', State))

Above is the full string that i'm creating as from the following expression -

='http://chart.googleapis.com/chart?&cht=map'

& '&chs=600x480' /* Chart size */

//& '&chld=GB-NTH|GB-BIR|GB-KEC' /* Country or County */

& '&chld=' & concat(DISTINCT 'GB-'& State, '|', fieldIndex('State', State))

& '&chdl=' & concat(DISTINCT 'GB-'& State, '|', fieldIndex('State', State)) /* Legend */

& '&chco=B3BCC0|5781AE|FF0000|FFC726|885E80|518274' /* Color for no value */

& '&chtt=Test+Google+API+Map+Chart' /* Chart Title */

& '&chm=' & concat(DISTINCT 'fGB-' & State & ' Sales:' & Sales & ',000000,0,' & fieldIndex('State', State) & ',15', '|', fieldIndex('State', State))

& '&chma=10,110,10,10' /* Chart margins */

//& '&chf=bg,s,c6eff7' /* CBackground Colour */

Any help would be greatly appreciated? I've attached what i have done so far in the QVW

Thanks

Lewis

[rwunderlich · Accepted solution]

space-cadet wrote:I'm trying to use fieldIndex('State', State) but that is not really doing what i want it to

Sounds like you need to subtract one from your sequence#. The syntax for doing that in your string concatenation would be:

text(fieldIndex('State', State)-1)

[#3]

Rob, thanks you very much for your help!

Everything back in its rightful place now

Thanks

Lewis

[rwunderlich]

Nice piece of work!

[#5]

Hi space-cadet

Could you tell me how you did this as I am also incorporating Google maps in my QV docs.

Thanks