Generate and export KML(XML) file through text object in QlikView (migrate to QlikSense)

[NicolasAimain1]

Dear every body,

A Customer has a text object where XML/KML file is build upon selection and copied/paste into text file (.txt). It has a static part and a dynamic part (which can raise a maximum of 1000 placemark which is the limit of the tool in which KML are loaded). I think the text file can raise 1 million of character (each placemark is around 1000 character, including data and KML code)

Text object on SAAS has limitation on exporting its content, we can’t export more than 20 or 30 placemark - I guess there is a limit of 10000 character in export functionnality or maybe it's an excel limitation as all the content is exported in only one cell. If we exceed, the cell in excel file is empty

The only workaround found until now is to create a table and export it in an xls file but it adds double quote to each line preventing us from using it without manual (and dangerous) modifications of exported data.

We can't use store to CSV as the content depends on selection.

Do you see any solution to migrate such needs in QlikSense ?

Thanks in advance

here is the code in the text object  and find in image attached a sample of the result

=if(count(ID)>vCalcLimitation,
'Too many ID $(vCalcLimitation) max, please filter'
,
'<?xml version="1.0" encoding="UTF-8"?>
<kml xmlns="http://www.opengis.net/kml/2.2">
<Document>'&
Concat(DISTINCT
'<Style id="'&icon&'">
<IconStyle> <Icon> <href>https://xxxxxxxxxxxx.nl/icones/'&icon&'</href> </Icon></IconStyle>
</Style>'
)
&chr(13)&chr(10)&



Concat(DISTINCT
'<Placemark>'&chr(13)&chr(10)&
'<name>'&replace(replace(replace(replace(ID,' ','_'),chr(39),'_'),chr(34),'_'),chr(38),'_')&'</name>'&chr(13)&chr(10)&
chr(09)&'<description><![CDATA['& replace(replace(replace(replace(replace(replace(replace([Commentaires libre],' ','_'),chr(39),'_'),chr(34),'_'),chr(38),'_'),chr(146),'_'),chr(147),'_'),chr(148),'_')&']]></description>'&chr(13)&chr(10)&
chr(09)&'<styleUrl>#'&icon&'</styleUrl>'&chr(13)&chr(10)&
chr(09)&chr(09)&'<ExtendedData>'&chr(13)&chr(10)&
chr(09)&chr(09)&'<Data name='&chr(39)&'Status'&chr(39)&'><value>'&Status&'</value></Data>'&chr(13)&chr(10)&
chr(09)&chr(09)&'<Data name='&chr(39)&'priority:'&chr(39)&'><value>'&priority&'</value></Data>'&chr(13)&chr(10)&
chr(09)&chr(09)&'<Data name='&chr(39)&'Adress'&chr(39)&'><value>'&Address&', '</value></Data>'&chr(13)&chr(10)&
chr(09)&chr(09)&'<Data name='&chr(39)&'Category'&chr(39)&'><value>'&Catégorie&'</value></Data>'&chr(13)&chr(10)&
chr(09)&chr(09)&'<Data name='&chr(39)&'Nature'&chr(39)&'><value>'&Nature&'</value></Data>'&chr(13)&chr(10)&
chr(09)&chr(09)&'<Data name='&chr(39)&'WAZE'&chr(39)&'><value>'&'https://waze.com/ul?ll='&num(var_mid_lat, '##############', '.', ',' )&','&num(var_mid_long, '##############', '.', ',' ) &'&amp;navigate=yes'&'</value></Data>'&chr(13)&chr(10)&
chr(09)&chr(09)&'<Data name='&chr(39)&'GMAPS'&chr(39)&'><value>'&'https://www.google.com/maps/place/'&num(var_mid_lat, '##############', '.', ',' )&','&num(var_mid_long, '##############', '.', ',' ) &'</value></Data>'&chr(13)&chr(10)&
chr(09)&chr(09)&'<Data name='&chr(39)&'File'&chr(39)&'><value>'&%FOLDER&'</value></Data>'&chr(13)&chr(10)&
chr(09)&chr(09)&'<Data name='&chr(39)&'Tag'&chr(39)&'><value>'&Tag&'</value></Data>'&chr(13)&chr(10)&
chr(09)&chr(09)&'<Data name='&chr(39)&'icon'&chr(39)&'><value>'&icon&'</value></Data>'&chr(13)&chr(10)&
chr(09)&chr(09)&'</ExtendedData>'&chr(13)&chr(10)&
chr(09)&'<Point><coordinates>'&replace(longitude,',','.')&','&replace(latitude,',','.')&',0</coordinates></Point>'&chr(13)&chr(10)&
'</Placemark>'&chr(13)&chr(10)
)
&

'</Document>'&chr(13)&chr(10)&'
</kml>'
)

[Marcus_Spitzmiller]

Hi, have you explored Qlik Application Automation?  Perhaps it could write that selection to a file somewhere or even accomplish whatever integration you are doing more efficiently.

[NicolasAimain1]

Thanks for the answer

Unfortunately, he's not on saas.

As a workaround, we put data in a table then export and copy paste the content in word to avoid double quote, then copy/paste the content in a "template" kml which already contain header and bottom of the KML file

For information, a text object in QlikSense can contain up to 65536 char but can only export 32767.

[jonnwarne]

Hi, can you share more details about it, then will be helps to share my views on it,

[NicolasAimain1]

@jonnwarne Here is a sample of the functionnality

This example is only one location but it can raise up to several thousand location. In qlikview we can copy the whole kml in one clic where the qlik sense version requires several action as we can't use the text object to export 

[marcus_sommer]

The biggest part of your content are the redundant tags. Therefore it might be a possibility to replace them with a simple numeric index of <1> and <2> for <placemark> and <name> and so on and then there is an appropriate replacing applied within the text-file. If I look on the following links:

search - Notepad++ macro for changing a certain part of a string - Super User

Can I search and replace in a Notepad++ macro? - Super User

it seems that editors like Notepad++ would be capable to do such job with macros. If not such measurements are surely possible with VBA/VBS.

[NicolasAimain1]

Thanks for the answer

For the moment, we created a template kml file containing beginning and end of the kml file. For each placemark we stored the whole code in a field called "placemark", removing every chr(10)/CR "Carriage Return" and chr(12)/LF "Line feed" that were used for indentation.
When we use this field in a table, if we download data in xlsx, there won't be any double quote until there are CR/LF character which allow to copy /paste the whole column in the template file

it 's a much more acceptable solution as this only need one template and some data cleaning and user can still get his result in one click or two