We should use ApplyMap instead of Join
ApplyMap will give better performance
ApplyMap table should be loaded first before it's use.
At the end of the script, this table will disappear !
But we need to make sure that ApplyMap table should have only two column/dimensions.... First should be the lookup dimension and second should be the value we need to get
There should not be any duplication in ApplyMap otherwise it will create duplicated rows..
Example:
ProductTable:
Mapping Load * Inline
[
Product, Name
P1, Product 1
P2, Product 2
P3, Product 3
];
Sale:
Load *, ApplyMap('ProductTable',ProductID, 'Unknown') as PartNameINLINE
[
Customer, ProductID
C1, P1
C2, P2
C4, P4
];
Here ApplyMap will refer to the ProductTable ... It will match ProductID of Sale table with Product of ProductTable and give PartName (2nd Dimension of ProductTable). Wherever there is no Product compare to ProductID, it will give ProductName as Unknown (Third Parameter)....
