GEOOPS-402 Error after Migrating from GeoAnalytics

[CHansford]

I'm in the process of migrating some of our apps from Qlik Sense to Qlik Sense SaaS.  As a result I need to convert some Easting/Northing points, everything works well using the GeoAnalytics Connector.  However when I try this in SaaS it gives me a GEOOPS-402 error

I tried changing to INTABLE, but that resulted in a 'GEOOPS-401 : Unknown column requested: SITE_ID'

Original Loadscript (GeoAnalytics Connector)

GeoPreload:

LOAD

    SITE_ID,

    EASTING,

    NORTHING

FROM [lib://Site Table - April 26, 2021.xlsx]

(ooxml, embedded labels, table is Sites)

LIB CONNECT TO 'QGA';

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

[_inlineMap_]:

mapping LOAD * inline [

_char_, _utf_

"'", '\u0027'

'"', '\u0022'

"[", '\u005b'

"/", '\u002f'

"*", '\u002a'

";", '\u003b'

"}", '\u007d'

"{", '\u007b'

"`", '\u0060'

"´", '\u00b4'

"              ", '\u0009'

];

IF FieldNumber('SITE_ID', 'GeoPreload') = 0 THEN

                call InvalidInlineData('The field SITE_ID in GeoPreload is not available');

END IF

IF FieldNumber('NORTHING', 'GeoPreload') = 0 THEN

                call InvalidInlineData('The field NORTHING in GeoPreload is not available');

END IF

IF FieldNumber('EASTING', 'GeoPreload') = 0 THEN

                call InvalidInlineData('The field EASTING in GeoPreload is not available');

END IF

Let [GeoDatasetInlineTable] = 'SITE_ID' & Chr(9) & 'NORTHING' & Chr(9) & 'EASTING';

Let numRows = NoOfRows('GeoPreload');

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 'SITE_ID', 'NORTHING', 'EASTING'

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

                                Next

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

                Next

                [GeoDatasetInlineTable] = [GeoDatasetInlineTable] & chunkText;

Next

chunkText=''

[GeoDataset]:

SQL SELECT [SITE_ID], [GeoDataset_Geometry] FROM Load(dataset='GeoDataset')

DATASOURCE GeoDataset INLINE tableName='GeoPreload', tableFields='SITE_ID,NORTHING,EASTING', geometryType='POINTLATLON', loadDistinct='NO', suffix='', crs='EPSG:27700' {$(GeoDatasetInlineTable)}

;

tag field [SITE_ID] with '$primarykey';

tag field [GeoDataset_Geometry] with '$geopoint';

tag field [SITE_ID] with '$geoname';

tag field [GeoDataset_Geometry] with '$relates_SITE_ID';

tag field [SITE_ID] with '$relates_GeoDataset_Geometry';

[GeoDatasetInlineTable] = '';

/* End GeoAnalytics operation Load ----------------------------------- */

I believe I have followed the Migrating from GeoAnalytics Connector to Qlik Sense GeoOperations to get the following GeoOperations Script.

[GeoDataset]:

Load * Extension GeoOperations.ScriptEval('

SELECT [SITE_ID], [GeoDataset_Geometry] FROM Load(dataset="GeoDataset")

DATASOURCE GeoDataset INLINE tableName="GeoPreload", tableFields="GeoPreload.SITE_ID,GeoPreload.NORTHING,GeoPreload.EASTING", geometryType="POINTLATLON", loadDistinct="NO", suffix="", crs="EPSG:27700" {$(GeoDatasetInlineTable)}

');

Any assistance would be gratefully received as I have a large number of this kind of conversion to do.

If only we could use Eastings and Northings directly in Qlik without having to convert to Lat/Long

Regards and Stay safe.

CHris

[Patric_Nordstrom · Accepted solution]

@CHansford 

The easiest way is to store the excel file as csv in cloud folder for instance dropbox. Datasource INTABLE doesn't support crs (like CSV and GEOFILE does) yet, hope to add that during 2021.

// Copy direct link from dropbox, replace www with dl
// https://dl.dropbox.com/s/anhnu66sl60jsj3/bng_points.csv?dl=0

[bng_conv]:
Load * Extension GeoOperations.ScriptEval('
Select SITE_ID, bng_conv_pts_Geometry from
Load()
  DATASOURCE bng_conv_pts CSV 
  	url="https://dl.dropbox.com/s/anhnu66sl60jsj3/bng_points.csv?dl=0",
    keyField="SITE_ID",
	fieldSeparator="COMMA",
    geometryField="NORTHING,EASTING",
    geometryType="PointLatLon", 
    crs="EPSG:27700" 
');

Thanks,

Patric

[CHansford]

Patric,

Thanks for the easy work around,  that has solved the issue and being able to do this in GeoOperations has increased the number of sites being converted.  Good to hear that this is being looked at, especially as not all organisations/Government departments allow the use of Dropbox to carry this out and just being able to use the SaaS Datafiles construct would solve some or even all of these issues.

 Could you add this to the GeoOperations information as had this information been available then I wouldn't have had to submit a question.  Also just like to say I thoroughly enjoyed the GeoOperations 'Do more with Qlik' webinar last week.

Regards, stay safe

CHris

[Patric_Nordstrom]

Hi Chris,

Yes, the help specifies the available parameters, https://help.qlik.com/en-US/geoanalytics/Subsystems/GeoOperationsService/Content/connector/connector...

Will also add my example to the GeoOperations https://community.qlik.com/t5/Qlik-GeoAnalytics-Documents/GeoOperations-Examples-Qlik-GeoAnalytics/t...

Yes, access to DataFiles from GeoOperations is on the way, hopefully also during 2021.

Glad you liked it, the session is also available on demand:

https://go.qlik.com/Do_More_with_Qlik_Webinar_Series_Target_Registration-LP.html

Thanks,

Patric

[psublue98]

Hello - I'm getting the same GEOOPS-402 error when trying to dissolve() a geofile while using a qvd. Searching for examples show LOCATIONSERVICE use with dissolve but I haven't come across a GEOFILE example. Below is the script, any thoughts on this error?

STATE_TO_REGION_LOOKUP:
LOAD
STFIPS,
STATE_ABBR,
STATE_NAME,
REGION
FROM [lib://DataFiles/STATE_TO_REGION_LOOKUP.qvd] (qvd);


DISSOLVE_OPERATION_OF_GEOFILE_RENAME_FIELDS:
Load * Extension GeoOperations.ScriptEval('
SELECT 
 GEOID as STFIPS, 
 STUSPS as STATE_ABBR, 
 NAME as STATE, 
 Geometry 
FROM
Dissolve(
 dissolveField="REGION",
 resolution="Source",
 areaDatasetKeyField="STFIPS",
 dissolveDataset="STATE_TO_REGION_LOOKUP",
 areaDataset="census_states",
 precision="6"
) 
DATASOURCE census_states GEOFILE 
url="https://www2.census.gov/geo/tiger/GENZ2020/shp/cb_2020_us_state_5m.zip", 
keyField="",
type="Shape", 
encoding="",
expectedGeomType="Polygon",
crs="EPSG:4269"
',STATE_TO_REGION_LOOKUP);

[Patric_Nordstrom]

Fyi, the CSV workaround is not needed anymore, CRS is now supported as a parameter also to INTABLE.

https://help.qlik.com/en-US/geoanalytics/Subsystems/GeoOperationsService/Content/connector/connector...

Thanks,

Patric

[CHansford]

@Patric_Nordstrom , Patric, am I to believe that INTABLE can now utilise crs?  I wonder as I now issues with Organisational issues using csv and dropbox to convert the Easting and Northing to Lat Long.  If this is the case what would the GeoOperations script look like as I have tried a number of different options and I continually get 'GEOOPS-401: The dataset Dataset does not contain any geometries.'

Could you add this particular conversion use case to your GeoOperations examples as a number of people I know are struggling with the same issue. 

Regards

Chris

[Patric_Nordstrom · Accepted solution]

Hi, yes INTABLE now supports crs. Here's a small example:

// https://gridreferencefinder.com/#gr=TQ3003480381|London|1,NS6010765646|Glasgow|1,NT2584774007|Edinburgh|1

BritishPoints:
LOAD * Inline [
SITE_ID,EASTING,NORTHING,lat,long
London,530034,180381,51.507407,-0.12772404
Glasgow,260107,665646,55.863630,-4.2368840
Edinburgh,325847,674007,55.953344,-3.1891057
];

[ConvertedPoints]:
Load * Extension GeoOperations.ScriptEval('
Select SITE_ID, NORTHINGEASTINGPoint from
Load()
DATASOURCE GeoDataset INTABLE keyField="SITE_ID", 
latitudeField="NORTHING", longitudeField="EASTING", crs="EPSG:27700"
',BritishPoints);

[CHansford]

@Patric_Nordstrom, Perfect, worked a dream, Thanks Patric.