Hello,
an example in which I use mapping instead of joining is when I have a Code field (for example, Country Code) and instead of joining it to the transactions table to retrieve the Country Name, I map the Code to the Country Name.
I first create a mapping table (that, by the way, will not remain in the data model with no need to drop it):
1.- Mapping:
Countries:
mapping load
Code,
Name as CountryName;
SQL Select *
FROM MYDB.dbo."CountryTable";
Then, when I load the customers, I map the Country Code field:
Customers:
LOAD
"No_" as CustomerID,
Name as CustomerName,
applyMap ('Countries', "Country Code",'No country assigned') as CountryName;
SQL Select *
FROM MYDB.dbo."CustomerTable";
2 .- Instead of mapping, you have the option to left join the customer table with the Countries:
Countries:
load
Code as CountryCode,
Name as CountryName;
SQL Select *
FROM MYDB.dbo."CountryTable";
TempCustomers:
LOAD
"No_" as CustomerID,
Name as CustomerName,
"Country Code" as CountryCode;
SQL Select *
FROM MYDB.dbo."CustomerTable";
Customers:
left join (TempCustomers) load * resident Countries;
drop table Countries;
drop table TempCustomers;
This option forces you to load the customer table twice, so in this case I prefer to do a mapping. As HIC says: The only time that you cannot use Applymap() instead of a join is if you want to fetch more than one single corresponding value (record) from the second table.
Hope this helps.
