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

[robert_mika]

Look at Mapping Load/ApplyMap

ApplyMap ‒ QlikView

(works in QS as well)

[karthikoffi27se]

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

[hic]

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

Don't join - use Applymap instead

HIC

[#5]

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

[#6]

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

Best,

Cindy

[ishanbhatt]

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.

[#8]

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]

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]

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;