Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Not applicable

GeoMakePoint() leave blank field

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

NULL.PNG.png

As empty is the map with Location as Dimension

Any idea?

Thank you

Luca

1 Solution

Accepted Solutions
Not applicable

Re: GeoMakePoint() leave blank field

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"

6 Replies
Employee
Employee

Re: GeoMakePoint() leave blank field

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

Not applicable

Re: GeoMakePoint() leave blank field

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

sense.gif

and GeoMakePoint() still fails.

What's wrong?

Employee
Employee

Re: GeoMakePoint() leave blank field

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

Not applicable

Re: GeoMakePoint() leave blank field

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

Not applicable

Re: GeoMakePoint() leave blank field

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"

Not applicable

Re: GeoMakePoint() leave blank field

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

Community Browser