topic Re: Google Map - 2 records with same address in QlikView

Google Map - 2 records with same address

Wed, 09 Oct 2013 10:02:39 GMT

Hi all,

This is the situation:

We have a list with for each store a number of postal codes of their customers.

eg:

store1 postalcode1

store1 postalcode2

store1 postalcode3

store2 postalcode4

store2 postalcode5

store3 postalcode6

...

He wants to see this in a google map.

I created a qvw with that google map and it seems to work correct. But if there are 2 stores with the same postal code, the longitude and latitude is correct, but there is no bubble for it.

Can someone explain me why there is no bubble for those 2 records?

See qvw in the attachment. Postcal code (= postnr) is 8700.

Thanks in advantage!

Fien

Re: Google Map - 2 records with same address

vikasmahajan — Wed, 09 Oct 2013 12:23:44 GMT

As per My Experience this is not possible in one chart you need to create other for the same.

Vikas

Re: Google Map - 2 records with same address

suniljain — Wed, 09 Oct 2013 12:32:25 GMT

Its not possible . Reason is that second id will be always overwritten by first one or vice versa. Regards; Sunil

Re: Google Map - 2 records with same address

pgrenier — Wed, 09 Oct 2013 12:41:42 GMT

Hello Fien,

It is the Sum() expression for the latitude and longitude that throws it off, it returns a latitude > 100 and a longitude > 7 (rather than ±50 and ±3.5). If you replace this expression by either Avg() or Max(), you shall see your missing dots.

Regards,

Philippe

Re: Google Map - 2 records with same address

richard_chilvers — Wed, 09 Oct 2013 12:44:37 GMT

Its an interesting problem.

What would you wish to show with the bubble for 2 stores? I notice your expression is MAX(omzet), which I would expect to be fine, but the last parameter (name & position) will have 2 values (shown when you hover over a bubble).

However, I have removed this 4th expression and still not got a result.

regards

Richard

Re: Re: Google Map - 2 records with same address

pgrenier — Wed, 09 Oct 2013 12:48:49 GMT

Please find attached the updated QVW file.

Cheers,

Philippe

Re: Google Map - 2 records with same address

richard_chilvers — Wed, 09 Oct 2013 12:51:22 GMT

Hi Philippe

Could you briefly explain your solution?

regards

Richard

Re: Google Map - 2 records with same address

Wed, 09 Oct 2013 13:09:59 GMT

Hi Philippe,

Thanks for the solution!

I changed the Sum() by Avg() and it works!

Kind regards,

Fien

Re: Google Map - 2 records with same address

pgrenier — Wed, 09 Oct 2013 13:10:28 GMT

Good morning Richard,

With pleasure!

You see, there are two expressions in the graph that are used to calculate the placement of the dots in the chart. The first one evaluates the latitude, and the second one evaluates the longitude.

Using the Sum() expression on a case where two distinct records should occupy the same space, which is the currently the case that Fien is facing, throws off the chart positionning based on the longitude and latitude values because the source dataset is doubled.

If we resume this to an analogy at it's simpliest level, we could imagine that these two expressions were currently used for positioning:

longitude= Sum(longitude)

latitude = Sum(latitude)

When your granularity level is at the Postal Code (postnr) level, and none of your entries overlap, this works fine.

In this case though, the granularity is at a level below (WinkID), but summarized at the postnr level. So, what we have as a result, is a chart longitude that is evaluated based on the sum of both individual longitudes, with the same effect happening at the latitude level. The dots are therefore out of the chart's range and set somewhere in lalaland.

Hope this helps,

Regards,

Philippe

Re: Google Map - 2 records with same address

pgrenier — Wed, 09 Oct 2013 13:13:18 GMT

Excellent

Please don't hesitate to mark the responses that were helpful and correct in order to mark this thread as answered.

Cheers,

Philippe

Re: Google Map - 2 records with same address

richard_chilvers — Wed, 09 Oct 2013 15:12:17 GMT

Hi Philippe

Since the website relaunch, I have not been able to mark posts as helpful or useful. Can you remind me where the facility is

regards

Re: Google Map - 2 records with same address

richard_chilvers — Wed, 09 Oct 2013 15:14:14 GMT

Ah - no stores in LalaLand then ?

Thanks for a good & clear explanation.

Richard

Re: Google Map - 2 records with same address

pgrenier — Wed, 09 Oct 2013 18:23:48 GMT

Hello Richard,

You may only mark as "Helpful Answer" or "Correct Answer" entries that are within a thread you created yourself.

Cheers,

Philippe