GeoMakePoint() leave blank field

[#1]

Hi all

I'm building a new test application with map but I can't get GeoMakePoint() to work.

I started with a large data set, but as I couldn't get it work, I tried the sample data from a previous post Qlik Sense Map

The data samples contains Latitude and Longitude field and I try to use GeoMakePoint() while loading data

LOAD

    CityID,

    AccentCity,

    Latitude,

    Longitude,

    GeoMakePoint(Latitude, Longitude) as Location,

    Population

FROM [lib://Mappe/cities sample.csv]

(txt, codepage is 1252, embedded labels, delimiter is ',', msq);

But still get the Location field empty

As empty is the map with Location as Dimension

Any idea?

Thank you

Luca

[hic]

I looks as if some Longitudes and latitudes are strings (left-aligned) and not numbers (right -aligned).

What happens if you insert

     IsNum(Longitude) as Debug,

in the script?

HIC

[#3]

Thank you Henric for your hint, I think you're right

I tried the IsNum() function and only few apparently random fields are treated as numbers, so I tried to force fields to number and text changing the load script because I don't know if GeoMaxePoint() expects text or numbers:

LOAD

    CityID,

    AccentCity,

    Latitude,

    Longitude,

    Population,

// Forcing Lat&Long to Text

    GeoMakePoint(Text(Latitude), Text(Longitude)) as LocationText,

// Forcing Lat&Long to Num

    GeoMakePoint(Num#(Latitude), Num#(Longitude)) as LocationNum,

// Checking if Num(Lat&Long) are numbers

    IsNum(Latitude) as NumLatOriginal,

    IsNum(Longitude) as NumLongOriginal,

// Checking if Num(Lat&Long) are numbers

    IsNum(Num#(Latitude)) as NumLat,

    IsNum(Num#(Longitude)) as NumLong

  FROM [lib://Mappe/cities sample.csv]

(txt, codepage is 1252, embedded labels, delimiter is ',', msq);

Nothing happens: fields are interpreted as text except the for aligned rigt with a -1 in NumLat or NumLong

and GeoMakePoint() still fails.

What's wrong?

[hic]

You may need a format code and decimal separator in the Num#() function, e.g.:

     Num#(Latitude, '#', ',')

You may need a Trim() function around the Latitude/Longitude:

     Num#(Trim(Latitude), '#', ',')

But I cannot say for sure where the problem is without seeing the raw data...

HIC

[#5]

Hi Henric

You was right: depends on how data are treated.

I copied the table from CSV to an excel sheet and imported: now Latitude and Longiture are treated as numbers and GeoMakePoint() works.

I've tried with Num#(Latitude, '#', ',') and Num#(Trim(Latitude), '#', ',') as you suggested but I couldn't get them to convert data in numbers

I attach the project with samples data if anyone want to try

[#6 · Accepted solution]

Try replacing . (dot) with a ,(comma) in Lon and lat

OMG took me a day to find out.

from

"51.441642";"5.469722";"Eindhoven"

to

"51,441642";"5,469722";"Eindhoven"

[#7]

Thank you Marco, that works.

I found that importing data as .xls also helps because that format doesn't use comma as separator.

Thank you

Luca

[Tyler_Waterfall]

Replying to a really old post, but I struggled with this as well -- until I realized that my latitude and longitude were written out with degree, minute, second.

I needed to convert these to decimal, as described a little bit here: https://www.latlong.net/degrees-minutes-seconds-to-decimal-degrees

There's probably a cleaner and simpler way to do this, but here is what worked for me:

LOAD *
, GeoMakePoint([Partner Location Latitude],[Partner Location Longitude]) as [Partner Location GeoPoint]
;
Load *
, If(WildMatch(tmp_lat,'*S')=1, -1,1)*(Subfield(tmp_lat,'°',1)+TextBetween(tmp_lat,'°',chr(39))/60+TextBetween(tmp_lat,chr(39),'"')/3600) as [Partner Location Latitude]
, If(WildMatch(tmp_lat,'*W')=1, -1,1)*(Subfield(tmp_long,'°',1)+TextBetween(tmp_long,'°',chr(39))/60+TextBetween(tmp_long,chr(39),'"')/3600) as [Partner Location Longitude]
;
LOAD
Trim(SubField("Latitude, Longitude",' ',1)) as tmp_lat,
LTrim(SubField("Latitude, Longitude",' ',-1)) as tmp_long,
...
;
SQL SELECT
...
"Latitude, Longitude"
FROM CSV (header on, delimiter ",", quote """") "CSV_source";