13 Replies Latest reply: Jul 1, 2014 2:23 AM by Ananth Reddy RSS

    ApplyMap

    Ananth Reddy

      Hi Qv,

       

      I'm facing problem with apply map.

      I have following script :

       

      Directory;

      Tab1:

      LOAD Id,

           Product,

           Sales

      FROM

      E:\Ananth_Works\Applymap.xlsx

      (ooxml, embedded labels, table is Tab1);

       

      Map:

      Mapping load

      Id,

      Sales

      Resident Tab1;

       

       

      Directory;

      Tab2:

      LOAD OrderId,

           Orders,

           Quantity,

           ApplyMap('Map',Id)*Quantity as Result

      FROM

      E:\Ananth_Works\app2.xlsx

      (ooxml, embedded labels, table is Sheet1);

       

       

      When I run this script I'm getting following error :

       

      Field not found - <Id>

      Tab2:

      LOAD OrderId,

           Orders,

           Quantity,

           ApplyMap('Map',Id)*Quantity as Result

      FROM

      E:\Ananth_Works\app2.xlsx

      (ooxml, embedded labels, table is Sheet1)

       

      Please help me whats wrong with this.

       

      Thanks & Regards,

      Ananth

        • Re: ApplyMap
          Anand Chouhan

          Try like

           

          If from table Applymap Product is not required then comment it and use same table instead to load resident table.

           

           

          Map:

          Mapping LOAD Id,

                       //Product,

                Sales

          FROM

          E:\Ananth_Works\Applymap.xlsx

          (ooxml, embedded labels, table is Tab1);

           

          //Map:

          //Mapping load

          //Id,

          //Sales

          //Resident Tab1;

           

          Tab2:

          LOAD OrderId,

               Orders,

               Quantity,

               ApplyMap('Map',Id)*Quantity as Result

          FROM

          E:\Ananth_Works\app2.xlsx

          (ooxml, embedded labels, table is Sheet1);

            • Re: ApplyMap
              Ananth Reddy

              Thanks for your response anand,

              I'm facing same error even after doing the same.

              But I don't want to do like this.

              Why resident load is not working and why this error "field not found"

              please help

                • Re: ApplyMap
                  Satyadev Jaiswal

                  Hi,

                   

                  You have to add OrderId in applymap script. Something like below,

                   

                  ApplyMap('Map',OrderId, 0)*Quantity as Result

                  • Re: ApplyMap
                    Anand Chouhan

                    Oh you need to rename the field Id as OrderId thats  why it is not found.

                     

                    Map:

                    Mapping LOAD Id as OrderId,

                                 //Product,

                          Sales

                    FROM

                    E:\Ananth_Works\Applymap.xlsx

                    (ooxml, embedded labels, table is Tab1);

                    • Re: ApplyMap
                      Anand Chouhan

                      Your complete script is

                       

                      Tab1:

                      LOAD Id as OrderId,

                           Product,

                           Sales

                      FROM

                      E:\Ananth_Works\Applymap.xlsx

                      (ooxml, embedded labels, table is Tab1);

                       

                      Map:

                      Mapping load

                      OrderId,

                      Sales

                      Resident Tab1;

                       

                      Drop table Tab1;

                       

                      Tab2:

                      LOAD OrderId,

                           Orders,

                           Quantity,

                           ApplyMap('Map',OrderId)*Quantity as Result

                      FROM

                      E:\Ananth_Works\app2.xlsx

                      (ooxml, embedded labels, table is Sheet1);

                  • Re: ApplyMap
                    AVIRAL NAG

                    Hi Ananth

                     

                    Replace Id with OrderId. As simple is that.

                    ApplyMap('Map',OrderId)*Quantity as Result

                     

                    Hope that works.

                     

                    Regards

                    Aviral Nag

                    • Re: ApplyMap
                      Ananth Reddy

                      Thanks to one and all.

                      My issue has resolved.

                       

                      Finally one thing I understood that, The key field should be same in both the tables right!

                      Correct me if i'm wrong...

                        • Re: ApplyMap
                          Satyadev Jaiswal

                          Hi,

                          The naming convention of the key field is not compulsory to be same in both the tables. However, you should have matching data in both the tables to achieve the result.

                           

                          You could have achieve the same result without renaming the field with below script,

                           

                          ApplyMap('Map',OrderId)*Quantity as Result

                           

                          Thanks

                          • Re: ApplyMap
                            Anand Chouhan

                            Yes right when do mapping the base key field name should be same in the tables

                             

                            Ex:-

                            Map:

                            Mapping Load

                            OrderId,

                            Sales

                            From Location;

                             

                            TableName:

                            Load

                            OrderId,

                            Quantity

                            ApplyMap('Map',OrderId) * Quantity as Result

                            From Location;

                             

                            Also the Id in the both field should be same then apply map the table.

                            • Re: ApplyMap
                              Anand Chouhan

                              In the base table basically the field should be same

                               

                              Ex:-

                              Map:

                              Mapping LOAD * Inline

                              [

                              Id,Desc

                              a,fsdf

                              b,saf

                              c,dfdsa

                              ];

                               

                              LOAD BaseId,Word,ApplyMap('Map',BaseId) as Description;

                              LOAD * Inline

                              [

                              BaseId,Word

                              a,23fd

                              b,45fdsf

                              c,3fsdf

                              d,56fdsf

                              e,566fsdf

                              ];