Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Announcements
Make your voice heard! Participate in the 2020 Wisdom of Crowds® Survey. BEGIN SURVEY
Highlighted
TimvB
New Contributor III

Convert Text to Number to create GeoPoint

Hi Experts, 

I want to create a GeoPoint with the function GeoMakePoint(). However, this does not work because my Latitude and Longitude fields are of type Text or Dual. I cannot figure out why some values are Text and others are Dual as this seems to be random. I tried converting the Longitude and Latitude fields to numbers without success. I tried the following:

  1. Longitude  * 1 as Longitude  
  2. Num#(Longitude) as Longitude  
  3. Num(Longitude) as Longitude  
  4. Sum(Longitude) as Longitude
  5. Trim(Longitude) as Longitude
  6. Num#(Trim(Longitude)) as Longitude 

Attached is a QVD (see screenshot) with sample data. I created an extra column with my expected result. I created this column by using the following:

'[' & Longitude & ',' & Latitude  & ']' as GeoPoint_Expected

This partially works in the map object, but it still gives me an error that some data points cannot be shown.

Does anyone know what the solution?

 

GeoPoint.PNG

3 Replies
Highlighted
Partner
Partner

Re: Convert Text to Number to create GeoPoint

hard to diganose. but did you try trim and check there are trailing spaces by any chance?

Also are the rows which gets tagged as rows the format the same as others?

Highlighted
TimvB
New Contributor III

Re: Convert Text to Number to create GeoPoint

I just tried the function Trim() but there are no leading spaces. The Text and Dual records both have "visually" same format, so I cannot notice any difference.

Highlighted
jamelmessaoud
Contributor

Re: Convert Text to Number to create GeoPoint

I came up with this same problem and I found that by doing this to the lat/long fields worked

Num#(Latitude, '#', ',') as Latitude,
Num#(Longitude, '#', ',') as Longitude

I would suggest doing the above in the temp table with your postcodes and then do a select from the table table and use the GeoMakePoint function

GeoMakePoint(Latitude, Longitude)

Like I said this seemed to work for me 🙂

Thanks