0 Replies Latest reply: May 19, 2017 8:59 AM by Vinay kumar Bangari RSS

    Address point Lookup in GeoAnalytics in QlikSense

    Vinay kumar Bangari

      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 ----------------------------------- */