Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Not applicable

Can Lookup function do this?

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 codeLanding Airport code
MCOATL
ATLMOB
LGASFO
DENMCO

Table 2:

 

 

Airport CodeCityStateLatitudeLongitude
MCOOrlando, FL16
ATLAtlanta, GA27
LGANew York, NY38
DENDenver, CO49
SFOSan Francisco, CA510

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

1 Solution

Accepted Solutions
ishanbhatt
Contributor II

Re: Can Lookup function do this?

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

15 Replies

Re: Can Lookup function do this?

Look at Mapping Load/ApplyMap

ApplyMap ‒ QlikView

(works in QS as well)

karthikoffi27se
Contributor III

Re: Can Lookup function do this?

Hi Cindy

You can use a mapping load as Robert says,

MAP_LOAD:

Mapping Load

          Takeoff Airport Code

          Landing Airport Code

From Table 1.qvd (QVD);

LOAD     

          Airport Code,

          City State,

          Latitude,

          Longitude,

          Applymap('MAP_LOAD',"Airport Code",'N/A') as Airport Code

FROM Table 2.qvd (QVD);


Many Thanks

Karthik

Employee
Employee

Re: Can Lookup function do this?

... and a blog post about the function that Robert and Karthik suggest:

Don't join - use Applymap instead

HIC

Not applicable

Re: Can Lookup function do this?

I appreciate your help, Karthik.

I noticed that you put ".QVD" file format at the end. Does that really matter? Right now, I have an Excel file "FROM [lib://.....xlsx]  .

Thanks,

Cindy

Not applicable

Re: Can Lookup function do this?

HIC, thanks a lot for the link. It is good to learn about the ApplyMap function are shared.

Best,

Cindy

ishanbhatt
Contributor II

Re: Can Lookup function do this?

HI Cindy,

Qlik provides look up similar functionality and its name is Apply map. To use this functionality you have to consider below points.

1. Make Mapping Table (Mapping Table must have only two columns).

2. Use Apply Map function in the table.

Please note that Create Mapping table first then use apply map function in the table.

You can find the detail Apply map functionality on below link.

Don't join - use Applymap instead

Hopefully, this is helpful to you.

Not applicable

Re: Can Lookup function do this?

Hi Bhatt:

Thank you for your reply.

However, my problem is Table 1 is my main table which has more rows (1000 rows). I want to get the airport code from table 2 (only has 100 rows), and then plot in the map (Long, Lat).

Can I use ApplyMap function twice? and then put back in my table 2 to plot the location? 

ApplyMap('Map_Load', "Airport Code" )as "Takeoff Airport",

ApplyMap('Map_Load', "Airport Code" )as "Landing Airport"

When I do that, my map does not work.

Thanks for your help,

Cindy

ishanbhatt
Contributor II

Re: Can Lookup function do this?

Hey Cindy,

Yes. We can use Apply Map function twice on the table. But let's discuss with your example.

//Try below script

/*Table1 has two different columns that contain the same values (Takeoff Airport code, Landing Airport code). For this, we have to make two mapping tables. One mapping table contains Takeoff data and the rest contains landing data.

Below is the mapping tables. For separation, I have defined table name with an activity prefix. you can change as per your need.*/

TakeoffTable2:

Mapping Load

AirportCode                                                                        AS [Take off Airport code],

Trim(CityState)&'|'&Trim(Latitude)&'|'&Trim(Longitude)      AS TakeoffCityLatLong

Resident

Table2_Temp;

LandingTable2:

Mapping Load

AirportCode  AS [Landing Airport Code],

Trim(CityState)&'|'&Trim(Latitude)&'|'&Trim(Longitude) AS LandingCityLatLong

Resident

Table2_Temp;

Drop Table Table2_Temp; //Where Table2_Temp is your main data source which contains all the City, Lat, and    Long info.

Table1:

Load

*,

SubField(TakeoffCityLatLong,'|',1) as TakeofffCityState,

SubField(TakeoffCityLatLong,'|',2) as TakeoffLatitude,

SubField(TakeoffCityLatLong,'|',3) as TakeoffLongitude,

SubField(LandingCityLatLong,'|',1) as LandingCityState,

SubField(LandingCityLatLong,'|',2) as LandingLatitude,

SubField(LandingCityLatLong,'|',3) as LandingLongitude;

Load

*,

ApplyMap('TakeoffTable2',[Take off Airport code],'NA') AS  TakeoffCityLatLong,

ApplyMap('LandingTable2',[Landing Airport Code],'NA') AS  LandingCityLatLong

Resident

Table1_Temp; //Where Table1_Temp contain your main data source of Table1.


Hopefully, this will help you.

Thanks,

Ishan

ishanbhatt
Contributor II

Re: Can Lookup function do this?

Below is my whole script.

Table1_Temp:

LOAD * INLINE [

    Take off Airport code, Landing Airport Code

    MCO, ATL

    ATL, MOB

    LGA, SFO

    DEN, MCO

];

Table2:

LOAD * INLINE [

    AirportCode, CityState, Latitude, Longitude

    MCO, "Orlando, FL", 1, 6

    ATL, "Atlanta, GA", 2, 3

    LGA, "New York, NY", 4, 5

    DEN, "Denver, CO", 5, 6

    SFO, "San Fransico, CA", 6, 2

];

Rename Table Table2 to Table2_Temp;

TakeoffTable2:

Mapping Load

AirportCode  AS [Take off Airport code],

Trim(CityState)&'|'&Trim(Latitude)&'|'&Trim(Longitude) AS TakeoffCityLatLong

Resident

Table2_Temp;

LandingTable2:

Mapping Load

AirportCode  AS [Landing Airport Code],

Trim(CityState)&'|'&Trim(Latitude)&'|'&Trim(Longitude) AS LandingCityLatLong

Resident

Table2_Temp;

Drop Table Table2_Temp;

Table1:

Load

*,

SubField(TakeoffCityLatLong,'|',1) as TakeofffCityState,

SubField(TakeoffCityLatLong,'|',2) as TakeoffLatitude,

SubField(TakeoffCityLatLong,'|',3) as TakeoffLongitude,

SubField(LandingCityLatLong,'|',1) as LandingCityState,

SubField(LandingCityLatLong,'|',2) as LandingLatitude,

SubField(LandingCityLatLong,'|',3) as LandingLongitude;

Load

*,

ApplyMap('TakeoffTable2',[Take off Airport code],'NA') AS  TakeoffCityLatLong,

ApplyMap('LandingTable2',[Landing Airport Code],'NA') AS  LandingCityLatLong

Resident

Table1_Temp; 

Drop Table Table1_Temp;