topic Re: Google Static map sorting issue in QlikView

Google Static map sorting issue

Anonymous — Thu, 13 Sep 2018 06:53:20 GMT

Hi,

I am trying to get a Google static world heat map to work in QlikView.

The code-string I am using in a text object as an image and it works just fine (in fact the map is shown and the coloring is given to the selected countries) - but however the sorting of the values is not correct:

http://chart.apis.google.com/chart?&cht=t&chs=440x220&chtm=world&chco=f5f5f5,EEF3F7,517992,052A47&chld=ATAUBECACH&chd=t:100,31,34,43,58

So according to above string the countries with the highest values should be Austria (AT) and Switzerland (CH) - which is not correct.

Does anybody have a suggestion to a solution for the correct values to be matched with the correct country code?

Thanks,

David

Re: Google Static map sorting issue

rwunderlich — Fri, 14 Sep 2018 16:44:41 GMT

What QV expression are you using to build the url?

Re: Google Static map sorting issue

Anonymous — Sat, 15 Sep 2018 06:46:30 GMT

The chart expression is as follows:

='http://chart.apis.google.com/chart?&cht=t'

& '&chs=' & '440x220' /* Chart size */

& '&chtm=' & 'world' /* Region */

& '&chco=' & 'f5f5f5' /* Color for no value */

& ',' & 'D5E3EC,517992,143C54' /* Color gradient */

& '&chld=' & concat(DISTINCT [Country code], '', fieldIndex('ISO', [Country code]))

& '&chd=t:' & concat(ceil(aggr(100*sum(_Sales)/max(TOTAL aggr(DISTINCT sum(_Sales), [Country code])), [Country code])), ',', fieldIndex('ISO', [Country code]))

LOAD script:

Tmp:

LOAD * INLINE [

Country code, _Sales

QA, 50142

SG, 266437

TH, 267775

SK, 810146

SA, 341680

RO, 439454

TW, 607048

PT, 313915

SE, 2720556

RU, 4151880

]

;

The rendered map parameters:

http://chart.apis.google.com/chart?&cht=t&chs=440x220&chtm=world&chco=f5f5f5,D5E3EC,517992,143C54&chld=PTQARORUSASESGSKT…

However the correct CountryCode / Sales pairs should be as:

Country code	=Sum(_Sales)

RU	4 151 880
SE	2 720 556
SK	810 146
TW	607 048
RO	439 454
SA	341 680
PT	313 915
TH	267 775
SG	266 437
QA	50 142

Thanks and BR,

David

Re: Google Static map sorting issue

rwunderlich — Sat, 15 Sep 2018 14:15:32 GMT

I don't see anything obvious. i suggest to start testing the expression a piece at a time in a text object.

-Rob