Converting an ID number to another via a common string

[AyCe1082]

Hi all,

I have a long list of images that are named after an ID number. I am looking to convert those names to a different numbering system used in a different database. Is it possible to convert these IDs to their counterparts by way of a common name?

For example:

ID Source 1    ID Name    ID Source 2

12345               Ferrari        56789

images/12345.png would be renamed to images/56789.png and render the same image in the UI.

[jochem_zw · Accepted solution]

you can use applymap function

so load the table from the other database (Database2) as a mapping table

MapNewNumber:

Mapping Load 
ID Name,
ID Source 2
From Database2;

In the table from DB1 use

Images:
Load
ID Source 1,
ID Name,
'images/' & applymap('MapNewNumber',ID Source 1,'Unknown') &' .png' as NewImageName
from Database1;

[AyCe1082]

Thanks, I will try that out!