Within operation example

[Sridhanabharathi]

Hi all,

I have used the examples from Connector Examples Qlik GeoAnalytics - Qlik Community - 1548148 and was able to run them fine. The code block that gets generated after successful completion of within operation is :

data:
load * Inline [
id,lat,lon
1,25.8587,-80.2094
10960,31.1073,-97.7479
22821,42.6722,-83.1335
];

LIB CONNECT TO 'GA';

/* Generated by GeoAnalytics for operation Within ---------------------- */

[_inlineMap_]:

mapping LOAD * inline [

_char_, _utf_

"'", '\u0027'

'"', '\u0022'

"[", '\u005b'

"/", '\u002f'

"*", '\u002a'

";", '\u003b'

"}", '\u007d'

"{", '\u007b'

"`", '\u0060'

"´", '\u00b4'

" ", '\u0009'

];

IF FieldNumber('id', 'data') = 0 THEN

call InvalidInlineData('The field id in data is not available');

END IF

IF FieldNumber('lat', 'data') = 0 THEN

call InvalidInlineData('The field lat in data is not available');

END IF

IF FieldNumber('lon', 'data') = 0 THEN

call InvalidInlineData('The field lon in data is not available');

END IF

Let [ptsInlineTable] = 'id' & Chr(9) & 'lat' & Chr(9) & 'lon';

Let numRows = NoOfRows('data');

Let chunkSize = 1000;

Let chunks = numRows/chunkSize;

For n = 0 to chunks

Let chunkText = '';

Let chunk = n*chunkSize;

For i = 0 To chunkSize-1

Let row = '';

Let rowNr = chunk+i;

Exit for when rowNr >= numRows;

For Each f In 'id', 'lat', 'lon'

row = row & Chr(9) & MapSubString('_inlineMap_', Peek('$(f)', $(rowNr), 'data'));

Next

chunkText = chunkText & Chr(10) & Mid('$(row)', 2);

Next

[ptsInlineTable] = [ptsInlineTable] & chunkText;

Next

chunkText=''

[WithinAssociations]:

SQL SELECT [pts_county_RelationId], [id], [LocationDbId] FROM Within(enclosed='pts', enclosing='county')

DATASOURCE pts INLINE tableName='data', tableFields='id,lat,lon', geometryType='POINTLATLON', loadDistinct='NO', suffix='', crs='Auto' {$(ptsInlineTable)}

DATASOURCE county LOCATIONSERVICE geometry='AREA', type='AADM2', country='US', serviceName='default'

SELECT [LocationDbId], [county_Geometry], [county_Name] FROM county;

[county]:

SQL LOAD * FROM county;

tag field [pts_county_RelationId] with '$primarykey';

tag field [LocationDbId] with '$primarykey';

tag field [county_Geometry] with '$geopolygon';

tag field [LocationDbId] with '$geoname';

tag field [county_Geometry] with '$relates_LocationDbId';

tag field [LocationDbId] with '$relates_county_Geometry';

[ptsInlineTable] = '';

/* End GeoAnalytics operation Within ----------------------------------- */

In this code, if i want to add my own kml file or shapefile(zip) from local folder, how do i edit this?For example the kml file after loading looks like:

MyKML:
LOAD
Agniyar.Name,
Agniyar.Area 
FROM [lib://Local_E_Drive /Water_resources/Riverbasin.kml]
(kml, Table is [Riverbasin/Riverbasin]);

I also would like to know if instead of inline table load, if we add an excel or csv files how the above code will get changed?