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;
