Indeed there is.
I have tried to make the problem "bitesize" so perhaps a bit more background is required.
I have a resident table as above which I intend to use as a Mapping Table.
But when I try to Map this table, the wrong column is mapped.
So yes, I have used:
And in the main table:
Applymap( 'Map', [Column 1]) as NewColumn,
But instead of getting Column 2 from the Mapping Table in NewColumn it inserts Column 1 again.
The logic by mapping is reverse. The first column is always the lookup-value and the second the return-value completely independent how they are named. Further the table must have only two columns and an applymap() on this will only take the first matching (in case there are several ones they won't be considered).
Yes and they are a great way of mapping information.
Normally I use inline tables.
This is the first time that I have tried to map from a resident load.
I would have thought that it is the same logic and process.
But no matter what I try (i.e. change the load order of the Resident Map table / go in Data Manager and swap the fields), the first column is mapped and not the second column.
I have now been able to get this to work.
using the default value helped
i.e. Applymap( 'Map', [Column 1], 1) as NewColumn,
I think the "error" was caused by the Columns not matching (a data matching error) and it seems to have repeated the Column 1 (from the MainTable) as the Default. This is what threw me.
I will mark this as solved.
Thank you for your assistance.
I think this is caused through the fact that there are no matching values between both tables and then the applymap() returned without setting the third parameter for non-matching values the used lookup-value. You could check it with:
Applymap( 'Map', [Column 1], 'no matching value available') as NewColumn,
This meant you need to take a closer look on the data within your columns and clean and/or format/convert them appropriate, maybe with something like: trim(), keepchar/purgechar(), upper/lower() or num#(), date#() and so on - so that both side are the same.