7 Replies Latest reply: Sep 26, 2011 9:23 AM by Aissam Boumejjane RSS

    selecting a field from another table

    Aissam Boumejjane

      Hi Guys,

       

      I have a two tables "Data" and "Route": One table contains data with all the shipment info, the other table "Route" is a inline table that I created.

       

      The first table does not contain the field "LimitDays". However the second (inline) Table has the field "LimitDays". The field limitdays contains the maximum days a shipment should not exceed.

       

      Now, I want to calculate the transittime and therefore created the following expression in the frist table (Data):

      if(networkdays(departure, arrival)<=LimitDays, 'OnTime', 'Late') as Status,

       

      Because the field LimitDays does not exits in the first table, I get an error message from qlikview (which ofcourse makes sence) .

       

      My question however is, how can I 'tell' qlikview to look for the limitdays in the second table (Route)? Is this btw possible?

       

      Thanks in advance!!!!

       

      iSam

        • selecting a field from another table

          hi,

           

          if the tables have an relation you can map the field and than you can make your if-statement.

           

          Regards,

          Lukas

            • Re: selecting a field from another table
              Aissam Boumejjane

              Hi Lucas,

               

               

               

              Thanks for your help! I’m still a newbie, so I’m sorry for my ignorance. I’ve looked up the map function. According to the Qlikview reference:  I should place ‘mapping’ before my load and . Scripts is now:

               

               

               

              Data:

              Mapping LOAD

               

                  as STT,

                   HAWB,

                   Origin,

                   Destination,

                   Origin & '|' & Destination as Route, 

                   num#(, '####.#') as GrossWeight,

                   num#(, '##.#')  as TotalVolume,

                   Date(DEP) as DEP,

                   Month(DEP) as Month,

                  if(NetWorkDays(DEP,POD+1)<=LimitDays, 'OnTime', 'Late') as StatusTest, 

               

               

               

              FROM

              (ooxml, embedded labels, table is DATA);

              mapping select FieldDays from Route; //(that didn’t work) so I tried ‘Map LimitDays using route;’ but that didn’t work either. L

               

               

               

              Route:

              mapping LOAD * INLINE [

                  Route, LimitDays

               

              DBX | ATW, 7

              ];

               

               

               

               

               

              That am I doing wrong?

               

               

               

              Thanks!

               

               

               

              iSam

                • Re: selecting a field from another table

                  You did some mistakes.Here it is ;-)

                   

                  Route:

                  LOAD * INLINE [

                      Route, LimitDays

                      DBX | ATW, 7

                  ];

                   

                   

                  Route_Mapping:

                  Mapping LOAD Route,

                                            LimitDays

                  Resident Route;            

                   

                   

                   

                  Data:

                  LOAD

                             if(NetWorkDays(DEP,POD+1)<=LimitDays, 'OnTime', 'Late') as StatusTest, 

                            *

                            ;

                  LOAD 

                      Re: selecting a field from another table as STT,

                       HAWB,

                       Origin,

                       Destination,

                       Origin & '|' & Destination as Route, 

                       num#(Re: selecting a field from another table, '####.#') as GrossWeight,

                       num#(Re: selecting a field from another table, '##.#')  as TotalVolume,

                       Date(DEP) as DEP,

                       Month(DEP) as Month,

                       applymap ('Route_Mapping',Origin & '|' & Destination,'n\a') AS LimitDays

                  FROM

                  Re: selecting a field from another table

                  (ooxml, embedded labels, table is DATA);

                   

                   

                    Hope it helps

                    • Re: selecting a field from another table
                      Aissam Boumejjane

                      Hi Lukas,

                       

                      Thanks for your help!!!  Unfortunately I still get the error that the field was not found.

                      I can't seem to find the mistake. I've attached my script, maybe you can find it:

                       

                      As i'm still learning i'm curious why you Loaded the below expression separately:

                       

                      Load

                      if(NetWorkDays(DEP,POD+1)<=LimitDays, 'OnTime', 'Late') as StatusTest,

                      *;

                       

                      You did the following for the applymapfunction: applymap('Route_Mapping', Route, 'n/a') AS LimitDays. What does the n/a stand for and why is it under quote?

                       

                      Thanks for all your help!!!

                       

                      iSam

                      • Re: selecting a field from another table
                        Aissam Boumejjane

                        Hi Lucas,

                         

                         

                         

                        I’m one step further. I solved the problem by removing: if(networkdays(DEP,POD)<=LimitDays, ‘OnTime’, ‘Late’) as StatusTest, from the datafield. And I’ve put route under quote.

                         

                        There “just” one last problem and that is that by mapping this I have a syntactic key. Now, If I change the name of “Route” there would not be a relation between the 2 tables. If I change LimitDays the mapping won’t work :S.  Do have an extra advice for me

                         

                         

                         

                         

                         

                         

                         

                        Regards,

                         

                         

                         

                        Isam

                          • Re: selecting a field from another table

                            Load

                            if(NetWorkDays(DEP,POD+1)<=Data.LimitDays, 'OnTime', 'Late') as StatusTest,

                            *;

                             

                            Data:

                            LOAD

                                [Tracking Number] as STT,

                                HAWB,

                                Origin,

                                Destination,

                                Origin & '|' & Destination as Route, 

                                num#([Gross Weight], '####.#') as GrossWeight,

                                num#([Total Volume], '##.#')  as TotalVolume,

                                Date(DEP) as DEP,

                                Month(DEP) as Month,

                                if(NetWorkDays(DEP,POD+1)<=LimitDays, 'OnTime', 'Late') as StatusTest,

                                ApplyMap('Route_Mapping',Origin & '|' & Destination, 'n/a') as Data.LimitDays,

                                'Q' & Ceil(Month(DEP)/3) as Quarter,

                                Date(DayStart(DEP)) as Date,

                                Date(POD) as POD, 

                                Date(ARR) as ARR,

                                Date(CCD) as CCD

                             

                            FROM

                            [..\Cis FokkerElmo.xlsx]

                            (ooxml, embedded labels, table is DATA);

                             

                            this could be work ;-)