Replacing Values in Multiple Columns Based on a Lookup Table

pstrauss Member
edited January 2023 in Magic ETL

So here's a challenge I can't seem to figure out how to solve in Magic ETL. I have a primary data source that has values written out as "tags" like "hc_res" and a secondary data source that maps those tables to pretty display names like "Healthcare Resources," which is what I need to display in reports. Normally, I'd just do a join to map these together, however my primary database has these values in multiple columns that need to use the same lookups. So for instance there is a column called "primary_inquiry_type" which might have "hc_res" in it, but there is another column called "secondary_inquiry_type" which also might have the value in it, and so forth.

Is there a way I can either do a replace operation on the tags across the board with the pretty display names in the secondary data source, or at least create additional columns for each one? Is my best solution to do multiple joins to the same lookup data source, or is there a better solution?