6 Replies Latest reply: Sep 3, 2015 6:38 PM by Massimo Grossi RSS

    Using ApplyMap() to map two different field names to the same field

    Rick Worthy

      Hi, I am trying to use a Mapping Table to do a lookup for a facility code and add a new fields as the facility location.  The facility code is used in several of the fields but they all have different names.  Is there a way to use the ApplyMap() function to map fields that have different names but similar content?  Something like ApplyMap('FacilityTable',FacilityCode=OfficeCode) as OfficeDesc,

       

      Here is my load script:  (Which is returning 0's and -1's in the OrigOfficeDesc and SalesOfficeDesc instead of the lookup field)

       

      FacilityTable:

      Mapping Load

           FacilityCode,

           FacilityDesc

      FROM

      [..\FacilityTable.xlsx]

      (ooxml, embedded labels, table is sheet1);

       

      Referrals:

      Load

           FacilityCode,

           ApplyMap('FacilityTable',FacilityCode) as OfficeDesc,

           OriginalFacilityCode,

           ApplyMap('FacilityTable',FacilityCode=OriginalFacilityCode) as OrigOfficeDesc

      FROM

      [..\Referrals.xlsx]

      (ooxml, embedded labels, table is sheet1);

       

      Sales:

      Load

           SalesFacility,

           ApplyMap('FacilityTable',FacilityCode=SalesFacility) as SalesOfficeDesc,

           DateOfSale,

           SalesAmount

      FROM

      [..\Sales.xlsx]

      (ooxml, embedded labels, table is sheet1);

       

      Obviously my syntax is incorrect in the ApplyMap() functions.  Is it possible to do this?  If so, what is the correct syntax for this operation?

       

      Cheers!

        • Re: Using ApplyMap() to map two different field names to the same field
          Kalyan Navuluri

          did you tried like this?

          Referrals:

          Load

               FacilityCode,

               ApplyMap('FacilityTable',FacilityCode) as OfficeDesc,

               OriginalFacilityCode,

               ApplyMap('FacilityTable',OriginalFacilityCode) as OrigOfficeDesc

          FROM

          [..\Referrals.xlsx]

          (ooxml, embedded labels, table is sheet1);

           

          will you able to share sample app and expected output

          • Re: Using ApplyMap() to map two different field names to the same field
            Gowtham Kesavan

            try lik this

            Load

                 FacilityCode,

                 ApplyMap('FacilityTable',FacilityCode) as OfficeDesc,

                 OriginalFacilityCode,

                 ApplyMap('FacilityTable',OriginalFacilityCode) as OrigOfficeDesc

            FROM

            [..\Referrals.xlsx]

            (ooxml, embedded labels, table is sheet1);

             

            Sales:

            Load

                 SalesFacility,

                 ApplyMap('FacilityTable',SalesFacility) as SalesOfficeDesc,

                 DateOfSale,

                 SalesAmount

            FROM

            [..\Sales.xlsx]

            (ooxml, embedded labels, table is sheet1);

            • Re: Using ApplyMap() to map two different field names to the same field
              Massimo Grossi

              this is the QlikView F1 help

               

              The ApplyMap function is used for mapping any expression to a previously loaded mapping table. The syntax is:

              applymap('mapname', expr [ , defaultexpr ] )

              where:

              mapname is the name of a mapping table that has previously been created through the mapping load or the mapping select statement (see Mapping). Its name must be enclosed by single, straight Quotation Marks in Scripting.

              expr is the expression, the result of which should be mapped.

               

               

              replace    ApplyMap('FacilityTable',FacilityCode=OriginalFacilityCode) as OrigOfficeDesc

              with          ApplyMap('FacilityTable',OriginalFacilityCode) as OrigOfficeDesc


              replace    ApplyMap('FacilityTable',FacilityCode=SalesFacility) as SalesOfficeDesc,

              with          ApplyMap('FacilityTable',SalesFacility) as SalesOfficeDesc,


                • Re: Using ApplyMap() to map two different field names to the same field
                  Rick Worthy

                  I did this and it worked.  Thank you!

                   

                  But to help my understanding of WHY it worked...if the field in the mapping table is named FacilityCode and the fields I need to apply the map to in the other tables are named OriginalFacilityCode and SalesFacility, ...how does Qlikview know to associate the mapping to the proper field?  Is it not looking at the field name in the mapping table but rather looking at the content of both fields and if one of them matches it returns the other?

                    • Re: Using ApplyMap() to map two different field names to the same field
                      Massimo Grossi


                      Is it not looking at the field name in the mapping table but rather looking at the content of both fields and if one of them matches it returns the other?

                      NO, it's looking at the first field and return the second; this is the QlikView F1 help:

                      A mapping table consists of two columns, the first containing comparison values and the second containing the desired mapping values.

                       

                       

                      this mapping table  

                      FacilityTable: Mapping Load    FacilityCode,     FacilityDesc

                      FROM [..\FacilityTable.xlsx]  (ooxml, embedded labels, table is sheet1);

                       

                      works in the same way as

                      FacilityTable: Mapping Load    FacilityCode as FieldA,     FacilityDesc as FieldB

                      FROM [..\FacilityTable.xlsx]  (ooxml, embedded labels, table is sheet1);


                  • Re: Using ApplyMap() to map two different field names to the same field
                    Sasidhar Parupudi

                    try like this

                     

                    FacilityTable:

                    Mapping Load

                         FacilityCode,

                         FacilityDesc

                    FROM

                    [..\FacilityTable.xlsx]

                    (ooxml, embedded labels, table is sheet1);

                     

                    Referrals:

                    Load

                         FacilityCode,

                         ApplyMap('FacilityTable',FacilityCode) as OfficeDesc,

                         OriginalFacilityCode,

                         ApplyMap('FacilityTable',OriginalFacilityCode,'NA') as OrigOfficeDesc

                    FROM

                    [..\Referrals.xlsx]

                    (ooxml, embedded labels, table is sheet1);

                     

                    Sales:

                    Load

                         SalesFacility,

                         ApplyMap('FacilityTable',SalesFacility,'NA') as SalesOfficeDesc,

                         DateOfSale,

                         SalesAmount

                    FROM

                     

                    [..\Sales.xlsx]

                     

                    (ooxml, embedded labels, table is sheet1);