3 Replies Latest reply: Nov 9, 2018 2:02 PM by Nagesh Setty RSS

    Qlik script equivalent of SQL's ToString()

    Mauritz Zastron

      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:

      SQL example.PNG

      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.