Some time ago I wrote a blog on Mapping functions and described how they can be used to replace or modify field values when you run the script. But how do you know when to map versus join the data in your data model? Mapping works well when you need to look up a single value in another table. For instance, you may have a products table with product data like the table below and you want to add the product category name to that table.
The product category name is in another table that looks like this:
Now you can add the ProductCategory field to the Products table by doing a join and that would work fine but you can also add the ProductCategory field by simply mapping.
Using a join:
Using a map:
Since we only want to add one value to the Products table, mapping is a safer option. With this small sample data, either will work but sometimes when you have a large data set, you have be cautious when using joins. You need to watch out for new records being added to the table as a result of the join thus potentially changing calculations.
While both a join and a map can work to combine data from two tables, in cases where only one value needs to be added, choose to map. It is an easier approach and it reduces the chance of errors being made in your data model. Now I am not saying that joins are bad and should not be used because that is not the case at all. I am simply stating that mapping should always be used versus a join when you only need one value.