Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Here are screenshots, I've also loaded the auto script that Qlik created. As you can see, I selected Florida as the state and Hollywood as the city, though its putting it as California. There is no way to geotag the city to the state.
Set dataManagerTables = '','CustTest';
//This block renames script tables from non generated section which conflict with the names of managed tables
For each name in $(dataManagerTables)
Let index = 0;
Let currentName = name;
Let tableNumber = TableNumber(name);
Let matches = 0;
Do while not IsNull(tableNumber) or (index > 0 and matches > 0)
index = index + 1;
currentName = name & '-' & index;
tableNumber = TableNumber(currentName)
matches = Match('$(currentName)', $(dataManagerTables));
Loop
If index > 0 then
Rename Table '$(name)' to '$(currentName)';
EndIf;
Next;
Set dataManagerTables = ;
Unqualify *;
__cityAliasesBase:
LOAD
Alias AS [__City],
geoKey AS [__geoKey],
CountryCode AS [__CityCountryCode]
FROM [lib://__GEO_TABLES/cityAliases.qvd]
(qvd);
__cityGeoBase:
LOAD
geoKey AS [__geoKey],
geoPoint AS [__GeoPoint]
FROM [lib://__GEO_TABLES/cityGeo.qvd]
(qvd);
__countryAliasesBase:
LOAD
Alias AS [__Country],
ISO3Code AS [__ISO3Code]
FROM [lib://__GEO_TABLES/countryAliases.qvd]
(qvd);
__countryGeoBase:
LOAD
ISO3Code AS [__ISO3Code],
ISO2Code AS [__ISO2Code],
Polygon AS [__Polygon]
FROM [lib://__GEO_TABLES/countryGeo.qvd]
(qvd);
__countryCodeAndCityName2Key:
MAPPING LOAD
__CityCountryCode & __City,
__geoKey
RESIDENT __cityAliasesBase;
__cityKey2GeoPoint:
MAPPING LOAD
__geoKey,
__GeoPoint
RESIDENT __cityGeoBase;
__countryName2IsoThree:
MAPPING LOAD
__Country,
__ISO3Code
RESIDENT __countryAliasesBase;
__countryCodeIsoThree2Polygon:
MAPPING LOAD
__ISO3Code,
__Polygon
RESIDENT __countryGeoBase;
[CustTest]:
LOAD
[Customer],
[Country],
[State],
[City],
[Rev],
[Profit],
APPLYMAP( '__cityKey2GeoPoint', APPLYMAP( '__countryCodeAndCityName2Key', APPLYMAP( '__countryName2IsoThree', LOWER([Country])) & LOWER([City])), '-') AS [CustTest.City_GeoInfo],
APPLYMAP( '__countryCodeIsoThree2Polygon', APPLYMAP( '__countryName2IsoThree', LOWER([Country])), '-') AS [CustTest.Country_GeoInfo]
FROM [lib://Maps/CityCustomersTest.xlsx]
(ooxml, embedded labels, table is CustTest);
TAG FIELD [City] WITH '$geoname', '$relates_CustTest.City_GeoInfo' ;
TAG FIELD [CustTest.City_GeoInfo] WITH '$geopoint', '$hidden', '$relates_City' ;
TAG FIELD [Country] WITH '$geoname', '$relates_CustTest.Country_GeoInfo' ;
TAG FIELD [CustTest.Country_GeoInfo] WITH '$geopolygon', '$hidden', '$relates_Country' ;
DROP TABLES __cityAliasesBase, __cityGeoBase, __countryAliasesBase, __countryGeoBase;
