topic Re: Dynamic Maps in QlikView

Dynamic Maps

Thu, 21 Mar 2013 17:27:59 GMT

Hello

I am trying to solve a complex problem in QlikView using maps.

Background:

In my data set that is a single excel data file I have 6 data feilds with following names - Lat1, Long1, Lat2, Long2, Lat3, Long3

These data columns contain latitude and longitude in them.

I have other three data fields Opt1, Opt2, Opt 3 (Option data fields) which contains values 1 or 0

Data:

So the data looks like this in my excel sheet:

Opt1	Lat1	Long1	Opt2	Lat2	long2	Opt3	Lat3	Long3
1	55	-1.522816	1	45	-1.5342	0	46	-1.6788
0	60	-1.513281	1	35	-1.5782	0	23	-1.7234
1	65	-1.465094	0	25	-1.5834	1	45	-1.8654
1	70	-1.465453	0	51	-1.5888	1	69	-1.5761

QlikView Objects: Now I need to have a UK map in Qlikview and a drop down box that contains option - Option1, Option2, Option3.

Action:

When I select Option1 from the drop down list then the map should show only the Lat1 and Long1 locations on the UK map that have Opt1 value equal 1.
When I select Option2 from the drop down list then the map should show only the Lat2 and Long2 locations on the UK map that have Opt2 value equal 1.
When I select Option3 from the drop down list then the map should show only the Lat3 and Long3 locations on the UK map that have Opt3 value equal 1.

**I tried to use the google maps but I need to have some key and when I search for that key the google web page says that they are not available**

Kindly help, If you need more info please let me know. I will provide more details.

Re: Dynamic Maps

Anonymous — Thu, 21 Mar 2013 17:49:24 GMT

Setting aside the google maps (you'll find many working solutions here), here is you can do.
Create a variable "Option" which can have only one of three values, Opt1, Opt2, Opt3. Use input box with drop-down.
Your expressions in maps will be using latitude and longitude:
.... lat... long ...

Replace with conditional in all expressions:
... pick(match(Option, 'Opt1','Opt2','Opt3'), lat1,lat2,lat3)...pick(match(Option, 'Opt1','Opt2','Opt3'), long1,long2,long3)

Regards,
Michael

Re: Dynamic Maps

Mon, 25 Mar 2013 10:06:22 GMT

Hello Michael

Thank you for your reply.

I got this white paper from QlikView website that talks about using Google maps in QlikView but it seems this approach will not work as I need a Key from Google which they have removed.

In addition can you elaborate the solution suggested by you in more details as I am new to QlikView.

What expression will exactly go in the expression section of the map.

Re: Dynamic Maps

Anonymous — Mon, 25 Mar 2013 13:38:45 GMT

That's correct, the key has been removed. You need to make changes in two places:

1. Instead of "http://maps.google.com/staticmap?" use "http://maps.googleapis.com/maps/api/staticmap"

2. instead of "key='&gmap_key" use "sensor=false"

Regards,

Michael

Re: Dynamic Maps

Tue, 26 Mar 2013 10:41:26 GMT

Hi Michael

Are you sure that the expression you suggested me will work as I think that google maps can only have one column for longitude and latitude in order to display.

Can you explain more about the expression as I am no able to understand it.

Re: Dynamic Maps

Anonymous — Tue, 26 Mar 2013 15:08:47 GMT

There is a ton of info about google maps on this forum. Here is one:

http://community.qlik.com/docs/DOC-3136

and, you can find a lot more. But make sure to implement the change I mentioned above.

Regards,

Michael

Re: Dynamic Maps

Tue, 26 Mar 2013 15:57:44 GMT

Hi Michael

Can you give me the exact expression that will go in the expression box as the expression you mentioned has got some dots.

Asheoran

Re: Dynamic Maps

Anonymous — Tue, 26 Mar 2013 17:15:46 GMT

Not sure it will help you because there are varuiables here, and I don't know if you have them, and the field names can be different.

1st expression

= avg( round (256*pow(2,($(var_zoom)-1)))+( longitude *((256*pow(2,$(var_zoom)))/360)) )

2nd expression

=avg(((256*pow(2,($(var_zoom)-1)))+((0.5*log((1+(sin((latitude)*pi()/180)))/(1-(sin((latitude)*pi()/180)))))*((-256*pow(2,$(var_zoom)))/(2*pi())))))

3rd expression is for the bubble size, whatever you use there.

And, the dynamic image expression on the colors tab:

='http://maps.googleapis.com/maps/api/staticmap?center='
& num(var_mid_lat, '##############', '.', ',' )
& ','
& num(var_mid_long, '##############', '.', ',' )
& '&zoom=$(var_zoom)'
& '&size='&map_size_x&'x'&map_size_y
& '&sensor=false'
& '&maptype='&var_maptype

Re: Dynamic Maps

Siva_Sankar — Wed, 27 Mar 2013 10:51:20 GMT

Asheoran, Received your mail. Attaching the qvw for you to get an idea.Also am attaching an excel sheet where you can use the tables individually and concatenate in the script to achive the solution. This may help. Regards. Siva

Re: Dynamic Maps

Wed, 27 Mar 2013 14:58:29 GMT

Hi Siva

Thanks a lot for helping thats really helpful of you.

Intially it was the data model that was creating trouble and was making everything complex. So I changed the data model and now it look like this.

Scenario_Number	Scenario_Detail	Latitude1	Longitude1
1	1	52.42819	-1.522816
1	1	52.404266	-1.513281
1	0	N	N
2	1	54.534146	-1.671421
2	1	53.542385	-0.721348
2	0	N	N
3	1	53.010782	-1.064692
3	1	52.297214	-0.834626
3	0	N	N

As compared to earlier in this data model I have replaced the Opt1,Opt2,Opt3,Opt4,... with one single column with name Scenario_Number and added another column with name Scenario_Detail which can only have 0s or 1s as value.

For a specific Scenario_Number, Scenario_Detail values tell which Latitude and Longitude should be displayed on map (when the value is 1 then that latitude & longitude should be displayed)

And all the Lat's and Long's are stored in just two columns - Latitude1 and Longitutde1 (If you look at the data set that I have attached with this post things will be more clear).

Now we can have a listbox/multibox via which we can select a Scenario_Number and corresponding values of Latitude1 & Longitude1 should be displayed.

I do not need to display any other dimention such as population etc.

So I worked on the the QlikView file that you gave me and loaded the data and changed few things in the script but its still not working.

Can you tell me where I am going wrong?

Re: Dynamic Maps

Anonymous — Wed, 27 Mar 2013 15:04:55 GMT

Problem #1 - there are no dimensions in your chart. The "Country" filed doesn't exist.

Problem #2 - you're using old expression for the dynamic image, with old url and with google key - see my notes above

Regards,

Michael

Edit: See attached. I only fixed your map dynmaic image, and replaced Country with 1.

Re: Dynamic Maps

Anonymous — Wed, 27 Mar 2013 15:24:47 GMT

One more attached. For illustration purposes, I added 3rd expression for the bubble size, changed dimension to Latitude1, and removed legend.

Re: Dynamic Maps

Wed, 03 Apr 2013 14:36:09 GMT

Hey Experts

You guys have been really helpful I was finally able to implement google maps in my QlikView document.

Now I am stuck on the map size. I tried to change it but was not able to.

Can anyone tell me if there is anyway we can change the size of the map.

Re: Dynamic Maps

Siva_Sankar — Thu, 04 Apr 2013 03:55:30 GMT

Try the following links. http://community.qlik.com/message/141277#141277 and http://community.qlik.com/message/248118#248118 Regards. Siva

Re: Dynamic Maps

Mon, 22 Apr 2013 09:26:47 GMT

Hi Guys

Now I am able to successfully plot the google map in Qlikview but I am stuck in coloring problem.

My data set contains 19,000 Long and Lat coordinates for a specific city and these 19,000 coordinates are grouped in 8 different segements or groups.

I want to display all these 19,000 coordinates but in different color segments.

So for eg. All coordinates in segement 1 will be red, all coordinates in segement 2 will be blue ....