Qlik Community

Qlik GeoAnalytics Discussions

Discussion Board for collaboration regarding Qlik GeoAnalytics.

vinay_bangari
Valued Contributor III

Address point Lookup in GeoAnalytics in QlikSense

I am getting following error and i am using below script please let me know if i am making any mistake.

bps

mto

/*

Operation: AddressLocationLookup

Operation Parameters

Search Text: address

Country: country

State: state

City: city

Postal Code: zipcode

Address Table

Name: Dataset

Type: Loaded Table

Table Name: t_address

Table Fields: id,address,country,state,city,zipcode

Geometry type: None

Only load distinct: Yes

*/

/* Generated by Idevio GeoAnalytics for operation AddressPointLookup ---------------------- */

[t_address_Distinct_Temp]:

LOAD distinct [id] AS tmpField_0, [address] AS tmpField_1, [country] AS tmpField_2, [state] AS tmpField_3, [city] AS tmpField_4, [zipcode] AS tmpField_5 resident t_address;

Let [DatasetInlineTable] = 'id' & Chr(9) & 'address' & Chr(9) & 'country' & Chr(9) & 'state' & Chr(9) & 'city' & Chr(9) & 'zipcode';

Let numRows = NoOfRows('t_address_Distinct_Temp');

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 'tmpField_0', 'tmpField_1', 'tmpField_2', 'tmpField_3', 'tmpField_4', 'tmpField_5'

row = row & Chr(9) & Replace(Replace(Replace(Replace(Replace(Replace(Peek('$(f)', $(rowNr), 't_address_Distinct_Temp'), Chr(39), '\u0027'), Chr(34), '\u0022'), Chr(91), '\u005b'), Chr(47), '\u002f'), Chr(42), '\u002a'), Chr(59), '\u003b');

Next

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

Next

[DatasetInlineTable] = [DatasetInlineTable] & chunkText;

Next

chunkText=''

DROP tables [t_address_Distinct_Temp];

[AddressPointLookupResult]:

SQL SELECT [id], [Dataset_Address], [Dataset_Geometry], [CountryIso2], [Dataset_Adm1Code], [Dataset_City], [Dataset_PostalCode], [Dataset_Street], [Dataset_HouseNumber], [Dataset_Match] FROM AddressPointLookup(searchTextField='address', country='country', stateField='state', cityField='city', postalCodeField='zipcode', streetField='', houseNumberField='', matchThreshold='0.5', service='default', dataset='Dataset')

DATASOURCE Dataset INLINE tableName='t_address', tableFields='id,address,country,state,city,zipcode', geometryType='NONE', loadDistinct='YES', suffix='', crs='Auto' {$(DatasetInlineTable)}

;

[DatasetInlineTable] = '';

/* End Idevio GeoAnalytics operation AddressPointLookup ----------------------------------- */

Community Browser