1 Reply Latest reply: Dec 20, 2017 11:23 AM by Marcus Sommer RSS

    Lookup values in different table based on priority

    Radu Panciuc

      Hi guys,

       

      I have 2 tables which I need to match based on several fields (looking at some references in Table 1 and retrieving data from Table 2 if the reference is found) but I would like to do that based on a certain priority.

      This means that I would like to check if Table1.Reference1 exists in Table2.Reference1 then retrieve a certain value from a different Field. If this doesn't exist then I would like to do the same for Table1.Reference2 to check if it exists in Table2.Reference2.

       

      Is this possible? If yes, what is best to link the 2 tables and retrieve the needed information.

       

      Thank you,

      Radu

        • Re: Lookup values in different table based on priority
          Marcus Sommer

          I think I would generate several mapping tables from Table 2 and then using a nested applymap() to map the values with the needed priority. I mean something like this:

           

          Map1:

          mapping load Reference1, Data1 from Table2;

           

          Map2:

          mapping load Reference2, Data2 from Table2;

           

          Table1:

          load *,

               applymap('Map1', Reference1, applymap('Map2, Reference2, '#NV')) as MappedValue

          from Source;

           

          - Marcus