Lookup values in different table based on priority

[Anonymous]

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

[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