Looking up values from secondary data source for multiple columns

pstrauss Member
edited January 2023 in Magic ETL

So I'm having some trouble wrapping my head around a solution for this scenario in Magic ETL:

-I have a primary data source with multiple columns where values are stored in shorthand like "hc_res" but I want to display reports with pretty names like "Healthcare Resources". The mapping of "hc_res" to "Healthcare Resource" exists in a second data source which updates on a pretty regular basis. The issue is that the shorthand names are stored in several columns (i.e. "primary_inquiry_type", "secondary_inquiry_type"), so I need to do the lookup for multiple columns during the ETL.

Normally, I'd use a join to do a lookup for a single column, but I don't think that would work in this scenario unless I did multiple joins - one for each column where the values need to be mapped. Is that my best bet, or is there a better way to achieve this?