Qlik Community

GeoAnalytics

Discussion Board for collaboration regarding Qlik GeoAnalytics.

Announcements
Now Live: Qlik Sense SaaS Simplified Authoring – Analytics Creation for Everyone: READ DETAILS
cancel
Showing results for 
Search instead for 
Did you mean: 
ppeterson1
Contributor
Contributor

How to Limit Load of Standard Areas and Point to smaller than Country Code

Hello, I am trying to pull in Geo Analytics Postal code information to build the actually Geo point information for map building purposes. To do this I first create an itterable list of country codes from our source table, being our ship to resource. I use this list to get a count of the number of loops and loop over the data using country code as a variable, mock up code will be posted below, this data is then added to a cleaned qvd that is wiped at the start of the script, and the loop cycles through each country code. So I am making several seperate calls to the server, the problem arises when a call exceeds 20k records, which I knew would happen, referencing the source docs: https://help.qlik.com/en-US/geoanalytics/Subsystems/GeoConnector/Content/connector/connector-geoanal... , at the Standard areas and Points Section of Dataset, it seems that there is not a smaller increment that I can limit the call to, such as "Country Code", "State", "City". I can add these variables but they are not used by the pregenerated GeoAnalytics script, would anyone be able to tell me how I would go about changing from the default of country code, to be able to limit the data to a lower level, such as mentioned above?

 

The Geo Script is:

/* Generated by GeoAnalytics for operation Load ---------------------- */
$(TableName):
SQL SELECT [LocationDbId], [ZipCodes_Geometry], [ZipCodes_Name] as ShippingZip, [CountryIso2], [ZipCodes_Adm1Code], [ZipCodes_Adm2Code], [LocationDbType] FROM Load(dataset='ZipCodes')
DATASOURCE ZipCodes LOCATIONSERVICE geometry='POINT', type='XPC', country='US' , serviceName='default'
;  //I would assume that I could add city/state in here, but does not seem to be the case
tag field [LocationDbId] with '$primarykey';
tag field [ZipCodes_Geometry] with '$geopoint';
tag field [LocationDbId] with '$geoname';
tag field [ZipCodes_Geometry] with '$relates_LocationDbId';
tag field [LocationDbId] with '$relates_ZipCodes_Geometry';
/* End GeoAnalytics operation Load ----------------------------------- */

 

 

Thanks,

 

Paul

 

/*Load the dataset that contains the Country Codes*/
Ship_TMP:
Load *
FROM [lib://Shiptotable]
(qvd);


LIB CONNECT TO 'GeoAnalytics Cloud Connector';

 

Let TableName = 'Zip_Codes_Transform';
/* Generated by GeoAnalytics for operation Load ---------------------- */
$(TableName):
SQL SELECT [LocationDbId], [ZipCodes_Geometry], [ZipCodes_Name] as ShippingZip, [CountryIso2], [ZipCodes_Adm1Code], [ZipCodes_Adm2Code], [LocationDbType] FROM Load(dataset='ZipCodes')
DATASOURCE ZipCodes LOCATIONSERVICE geometry='POINT', type='XPC', country='US' , serviceName='default'
;
tag field [LocationDbId] with '$primarykey';
tag field [ZipCodes_Geometry] with '$geopoint';
tag field [LocationDbId] with '$geoname';
tag field [ZipCodes_Geometry] with '$relates_LocationDbId';
tag field [LocationDbId] with '$relates_ZipCodes_Geometry';
/* End GeoAnalytics operation Load ----------------------------------- */


Store * From $(TableName) into [lib://Transform QVDs/$(TableName).qvd];

Drop Table $(TableName);

 

 

 

LIB CONNECT TO 'GeoAnalytics Cloud Connector';

/*Isolate the country codes*/
NoConcatenate
Country_CodeTMP:
Load Distinct
if(isNull(COUNTRY_CODE) or Trim(Text(COUNTRY_CODE)) = '' or COUNTRY_CODE = ' ' or COUNTRY_CODE = ',', 'Does Not Exist', Text(COUNTRY_CODE)) as COUNTRY_CODE

Resident Ship_TMP ;
Drop Table Ship_TMP;

NoConcatenate
CountryCodes:
Load Distinct
COUNTRY_CODE
Resident Country_CodeTMP where COUNTRY_CODE <> 'Does Not Exist';
Drop Table Country_CodeTMP;

Let NumCountryCodes = FieldValueCount('COUNTRY_CODE');

For i=1 to $(NumCountryCodes)

Let CountryCode = text(Peek('COUNTRY_CODE',$(i)-1));
Set chunkSize = 10000;
Set offset = 0;

$(TableName):
Load * From [lib://Transform QVDs/$(TableName).qvd](qvd) where CountryIso2 <> '$(CountryCode)';

do while FieldValueCount('LocationDbId') > offset


/* Generated by GeoAnalytics for operation Load ---------------------- */
SQL SELECT [LocationDbId], [ZipCodes_Geometry], [ZipCodes_Name] as ShippingZip, [CountryIso2], [ZipCodes_Adm1Code], [ZipCodes_Adm2Code], [LocationDbType] FROM Load(dataset='ZipCodes')
DATASOURCE ZipCodes LOCATIONSERVICE geometry='POINT', type='XPC', country='$(CountryCode)' , serviceName='default'
order by LocationDbId
limit $(chunkSize) offset $(offset)  /*this was a test to use the standard SQL offset and breaking the data into chunks which does not work, I would remove the Do..While loop and would not have chunksize or offset if I there is a way to limit by city / state, as it should not be needed. */
;
tag field [LocationDbId] with '$primarykey';
tag field [ZipCodes_Geometry] with '$geopoint';
tag field [LocationDbId] with '$geoname';
tag field [ZipCodes_Geometry] with '$relates_LocationDbId';
tag field [LocationDbId] with '$relates_ZipCodes_Geometry';
/* End GeoAnalytics operation Load ----------------------------------- */
Let offset = offset + chunkSize;

Loop




Store * From $(TableName) into [lib://Transform QVDs/$(TableName).qvd];
Drop Table $(TableName);




Next i;

Exit Script;

 

 

 

1 Solution

Accepted Solutions
Patric_Nordstrom
Employee
Employee

Sorry, there's no other filter besides country code.

Another option is to do NamePointLookup for all entries and check which doesn't resolve:

https://community.qlik.com/t5/Qlik-GeoAnalytics-Documents/How-to-improve-the-lookup-accuracy/ta-p/15...

To check valid names https://www.geonames.org/ is useful.

Thanks,

Patric

View solution in original post

1 Reply
Patric_Nordstrom
Employee
Employee

Sorry, there's no other filter besides country code.

Another option is to do NamePointLookup for all entries and check which doesn't resolve:

https://community.qlik.com/t5/Qlik-GeoAnalytics-Documents/How-to-improve-the-lookup-accuracy/ta-p/15...

To check valid names https://www.geonames.org/ is useful.

Thanks,

Patric