Qlik script equivalent of SQL's ToString()

[Mauritz_SA]

Hi everyone

I am reading data from a SQL database and storing it into QVDs (for the time being). One of the datatypes is a Geography datatype. I was wondering if there is a way to convert the Geography datatype to an nvarchar in Qlik when the source is a QVD using something similar to the ToString() function in SQL.

Below is an example of what I mean in SQL:

I am able to do this in Qlik script when I use the ToString() funtion when I read data from SQL. In future I will not have access to the SQL DB, but rather an abstracted layer built in QVD format.

I would like to know how I can get a value in the geography format (0xE6100000010DCDA9E3B4E40D41C07AA7C8BF10DE32400000000000004040) into a normalised format like in the screenshot above (POINT (18.8674430718306 -34.1085420714986 32)) when reading data from a QVD?

The script gives me an error when I try to perform the ToString() action in the Qlik Load statement:

LOAD

    Location,

    Location.ToString()

FROM [lib://SomeConnection/stdGeographicData.qvd] (qvd);

I presume the 0xE6100000010DCDA9E3B4E40D41C07AA7C8BF10DE32400000000000004040 value is stored as a string in the qvd and therefore cannot be converted. My final aim is to convert the normalised format polygons to polygons which can be interpreted by GeoAnalytics using a replace function I found online:

'[[' + REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(Location.ToString(),'POLYGON ',''),'(','['),')',']'),'], ',']],['),', ','],['),' ',',') + ']]'

Like I said, it works with my current SQL access, but I would like to know how I can do this when I only have access to qvds. Please let me know if anyone has any clever ideas how I can get around this.

[juraj_misina]

Hi,

I'm not aware of a Qlik function which could do this, but there might be a math behind, see here:

How to convert GPS Longitude and latitude from hex - Stack Overflow

Anyway, are you able to retrieve data from SQL and store it as string into QVDs while you have the DB available? If you store it in QVDs in correct format now, no need to worry about conversions in future.

Hope this helps

Juraj

[Mauritz_SA]

Hi Juraj

Thanks for the reply, I will definitely look into the math conversion. As for storing the the data into qvds and reusing it, that unfortunately won't work as the data can get updated by users in the application which generates the data stored in the SQL database. So I need to update the data in Qlik as it gets updated in the SQL DB.

I will look into your link and see if I can get a function to convert it. I will reply on your post if I can get it to work.

Thanks again.

Mauritz

[nsetty]

Hi Mauritz,

Is it possible to store new fields in QVD and retrieve later?  Assuming Location is of datatype "Geography"

LOAD ID,
    Location,
    LatNew,
    LongNew;
SQL SELECT ID,
    Location,
    Location.Lat AS LatNew,
    Location.Long AS LongNew
FROM [LOKA_DEMO_DEMO].[DBO].[stdGeographicData];

[Mauritz_SA]

Hi Nsetty

Unfortunately I can only get a SELECT * for the different tables. I have asked the IT team if they can build me a SQL View which performs my ToString() conversion.

Thanks for the reply!

Regards,

Mauritz