15 Replies Latest reply: Sep 6, 2017 11:52 AM by CINDY JIANG RSS

    Can Lookup function do this?

    CINDY JIANG

      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

        • Re: Can Lookup function do this?
          Robert Mika

          Look at Mapping Load/ApplyMap

           

          ApplyMap ‒ QlikView

           

          (works in QS as well)

          • Re: Can Lookup function do this?
            Karthik Selvaraj

            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

            • Re: Can Lookup function do this?
              Henric Cronström

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

              Don't join - use Applymap instead

               

              HIC

              • Re: Can Lookup function do this?
                ishan Bhatt

                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.

                  • Re: Can Lookup function do this?
                    CINDY JIANG

                    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

                      • Re: Can Lookup function do this?
                        ishan Bhatt

                        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

                          • Re: Can Lookup function do this?
                            ishan Bhatt

                            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;

                              • Re: Can Lookup function do this?
                                CINDY JIANG

                                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 CodeLanding Airport Code
                                MCOATL
                                ATLMOB
                                LGASFO
                                DENMCO

                                Table 2:

                                   

                                Airport CodeCityStateLatitudeLongitude
                                MCOORLANDO,FL28.43116-81.3081
                                ATLAtlanta, GA33.64073-84.4277
                                LGANew York, NY40.77693-73.874
                                DENDenver, CO39.8561-104.674
                                SFOSF, CA37.62131-122.379

                                 

                                 

                                Thank you so much for your help.

                                Cindy