Can Lookup function do this?

[#1]

Hi, everyone:

I am fairly new to the Qlik sense software. I am wondering what is a easy way for my problem. I have two tables as below.

Table 1:                                                                                     

Takeoff Airport code Landing Airport code MCO ATL ATL MOB LGA SFO DEN MCO

Table 2:

Airport Code CityState Latitude Longitude MCO Orlando, FL 1 6 ATL Atlanta, GA 2 7 LGA New York, NY 3 8 DEN Denver, CO 4 9 SFO San Francisco, CA 5 10

I would like to find the CityState's name from table 2 for where the airplane takeoff and land and them put on the map. Table 2 data has Longitude and Latitude for my map location. So I load the table2 as follow:

Load*, GeoMakePoint(Latitude, Longitude) as Location;

Load "Airport Code",

          CityState,

         Latitude,

          Longitude

From source;

Since both airports are looking up data from table 2, now I am confused. Does Lookup function can do it or I need to look for sth else? Thanks a lot for your help.

Cindy

[karthikoffi27se]

Hi Cindy,

I am at your ease!!. Please change the source to excel.

FROM

[lib://XXX/Table 1.xlsx]

(ooxml, embedded labels, table is Sheet1);

Kindly mark your comment as helpful or correct.

Many Thanks

Karthik

[#3]

Ishan:

I really appreciate your help. I changed the file to excel and used the exact codes as you did except the following one. However, it still gives me errors.

Table2_Temp:

LOAD

    AirportCode,

    CityState,

    Latitude, Longitude,

    GeoMakePoint([Latitude], [Longitude]) AS [Longitude_Latitude]

FROM [lib://files/QLIK practise applyMap.xlsx]

(ooxml, embedded labels, table is Table2);

TAG FIELD "Landing Airport Code" WITH '$geoname';

Below is my two tables that I created in Excel (I put both picture and file below).

Table 1:

Takeoff Airport Code	Landing Airport Code
MCO	ATL
ATL	MOB
LGA	SFO
DEN	MCO

Table 2:

Airport Code	CityState	Latitude	Longitude
MCO	ORLANDO,FL	28.43116	-81.3081
ATL	Atlanta, GA	33.64073	-84.4277
LGA	New York, NY	40.77693	-73.874
DEN	Denver, CO	39.8561	-104.674
SFO	SF, CA	37.62131	-122.379

Thank you so much for your help.

Cindy

[ishanbhatt]

Hey Cindy,

Considering the security, could you please send me the scrambled version of your application so that I can implement the solution in your app only. It would make easier for you to understand and we will not have to go back and forth.

Let me know your thoughts.

Apart from this, I am attaching a Qlik sense application here which contains the 3 possible solutions for this issue. You can give it a try.

Solution 1: Using Apply Map function.

Solution 2: Simple Join with Aliasing.

Solution 3: Keep the table containing the Geo information detached. We can use Lat & Long combination as a dimension in the map object and the create an expression using set analysis. This is a bit tricky so if its possible please send me the measure you are going to use in the map so that I can modify it at my end and you can use it afterward.

Thanks,

Ishan.

[#5]

Ishan:

Here is the scrambled version of my qvf file and data. Meanwhile, i will try your 3 possible solutions today. Much appreciated.

Cindy

[ishanbhatt · Accepted solution]

Hey Cindy,

Please find the attached QlikSense application(Which you sent me earlier). I've fixed the problem.

For my info What kind of Map you want to display? 

Thanks,

Ishan

[#7]

Ishan:

You are awesome. It works so well now with my data. Thank you SO much.

I use the Openstreet map. Below is the link from the Qlik help link.

http://tile.openstreetmap.org/${z}/${x}/${y}.png

Thank you and have a wonderful day!

Cindy