topic Re: Linking fields in QlikView

Linking fields

kinjal1645 — Wed, 25 Nov 2020 16:16:04 GMT

Hello,

I am plotting cities and its operator on the map. One city has more than one operator so I concatenated the operators against the city.

When I select geo_city I can see operators in that city.

But I when select m_operator I cannot see they are present in which cities. Since geo_city and geo_m_operator(this is concatenation of m_operator) are connected but geo_city and m_operator are not connected (above screenshot)

Let me know how to fix this.

You can also let me know if you have some other approach to display city and all its operators in different way so that fields city and operator are connected.

Thanks

Kinjal

stalwar1‌swuehl‌gwassenaar‌jagan‌tresesco‌

Re: Linking fields

MK9885 — Mon, 06 Aug 2018 18:00:58 GMT

maybe use

Upper(m_operator) as m_operator

since all your geo_m_operator values are in upper I.E Caps

Re: Linking fields

kinjal1645 — Mon, 06 Aug 2018 21:13:41 GMT

This doesn't work.

Re: Linking fields

bwisealiahmad — Mon, 06 Aug 2018 22:01:55 GMT

Hi,

The issue is your data model.

Your table with the m_operator field isn't connected to the rest of the data model...I understand you want to be able to chose a city and see connected operators and chose a operator and see connected cities?

Could you try with this in your script and then maybe you can add the mapping part to it?

Temp_Table:

LOAD

Upper(m_city) AS MobileCity,

Upper(m_operator) AS MobileOperator,

release AS Release

FROM

(biff, embedded labels, table is Sheet2$)

Where m_city <> Null();

City_Operator:

NoConcatenate

LOAD

MobileCity,

MobileOperator,

MobileCity&': '&Concat(MobileOperator,' ,') AS CityOperator,

MobileOperator&': '&Concat(MobileCity,' ,') AS OperatorCity

Resident Temp_Table

Group By MobileCity,MobileOperator;

Drop Table Temp_Table;

// Not sure about the mapping part below //

LET noRows = NoOfRows('City_Operator')-1;

FOR i=0 to $(noRows)

LET City =Peek('MobileCity',$(i),'City_Operator');

LET Operator=Peek('MobileOperator',$(i),'City_Operator');

GeoCode:

LOAD

'$(City)' AS MobileCity,

// '$(Operator)' AS MobileOperator,

[result/geometry/location/lat] AS Latitude,

[result/geometry/location/lng] AS Longitude

FROM [https://maps.googleapis.com/maps/api/geocode/xml?address=$(City)] (XmlSimple, Table is [GeoCode]);

Best,

Ali A