2 Replies Latest reply: Jul 9, 2012 3:04 PM by Stefan Wühl RSS

    Lookup at script level

      Hi,

       

      In the data source I have two tables connected via a key. Now, in one of the tables I have id and in the other table I have the description of this id.

      I want to show the id_desc as  a filter in the sheet. Can I do a lookup for the id_desc using the id value at the script level? If yes, can you please tell me how?

        • Re: Lookup at script level
          Deepak Kurup

          hi,

           

          Try this.

           

           

          Load id,key from table1;

           

           

          load desc, key from table2 where exists(key,key);

           

           

           

           

          Deepak

          • Re: Lookup at script level
            Stefan Wühl

            It would be good if you could post the table structures.

             

            I think there are multiple ways of doing what you want.

             

            a) If id is the key between the tables, you probably don't need to do anything. Your id_desc should be linked to your id, i.e. you can use in a sheet object expression as replacement to id or descriptive additional column.

             

            b) If latter, you could also (left) join the tables.

             

            c) You could reload your second table in a MAPPING LOAD, using only the fields giving the id and the description, and then use applymap() function to add the descriptive field to your first table or to any table that you only have the id in.

             

            d) Use lookup() function in the first table to access the descriptive field in the second based on id, something like

             

            LOAD

            id

            lookup('id_desc','id_field_in_second_table',id,'second_table_name') as id_description

            ...

            from Table1;