3 Replies Latest reply: Apr 9, 2012 3:18 PM by heydaraliyev RSS

    Newbie: vlookup between 2 tables on loadscript

    heydaraliyev

      I'm a little new at this and I've trolled the boards for a way to understand this, but I don't get it.  So thanks for any help.

       

      In my load script, I'm going to load two xls sheets.  Table 1 will be a list of charges.  Each charge has a cost code.

      in this example, Field 1 is a charge amount, and field2 is a cost code amount.

       

      Tables 2 will be a list of the codes.

      FieldA of this table will be the codes, FieldB will be some description of that code for example.

       

      I want to add a field to Table 1 that contains the descriptors of the cost codes.

       

       

      I looked into using "lookup" but that only works works for fields in the same table. 

       

      Thanks,

      H Aliyev

        • Newbie: vlookup between 2 tables on loadscript
          Dennis Hoogenboom

          Try this in your load script:

           

          Data:

          Load

                    Code as CostCode ,                 

                    Charges

          From Excelfile1.xls;

           

           

          Join (Data)

          Load

                    Code2 as CostCode ,        //must be the same name as above                

                    Descriptors

          From Excelfile2.xls;

           

           

          This will add the Descriptors field to your Data based on the CostCode.

          Is this what you are looking for?

           

           

          Good luck,

          Dennis.

          • Newbie: vlookup between 2 tables on loadscript
            Jason Michaelides

            Dennis's solution will work and also have the benefit of giving all CostCodes and Descriptions even if they are not present in the Charges tables.

             

            Generally though, for this kind of lookup you would use mapping with ApplyMap(). Look it up in the F1 help but in this case you would have the following in your load script:

             

            Map_CostCodes:

            MAPPING LOAD

                 CostCode,

                 Description

            FROM...;

             

            Data:

            LOAD

                 CostCode,   

                 ApplyMap('Map_CostCodes',CostCode,'<No Description>')     AS     CC_Description

            FROM....;

             

            Hope this helps,

             

            Jason

            • Newbie: vlookup between 2 tables on loadscript
              heydaraliyev

              That's it thanks guys.