Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
I have these two tables loaded:
Product Table:
Product ID
Product Code
Mapping Table:
Mapping Code (Product Code+Region Code)
Mapping Description
I want to load another table from an excel file:
Product Region:
Product ID
Region Code
1) I need to add a field to this table Product Code which needs to be derived from Product Table for the matching Product ID
2) I need to add Mapping Description derived from Mapping Table using the concatenated key of Product Code and Region Code.
Resulting Table:
Product ID
Region Code
Product Code - Sourced from Product Table
Mapping Description - Sourced from Mapping table
Is there a way to do it?
I think you can probably use a mapping load for the first two tables and apply map to create the final table. Here is a great explanation of this functionality:
Try with
Product Table:
mapping load
Product ID,
Product Code
from.............xls;
Mapping Table:
mapping load
Mapping Code ,
Mapping Description
from.............xls;
Product Region(result table):
Product ID,
Region Code,
Applymap('Product Table',Product ID,'NA') as Product Code,
Region Code ' & ' Product Code as Mapping Code,//concatinate both fields
Applymap('Mapping Table',Mapping Code,'NA') as Mapping Description
Your result table will be Product Region.
In my resulting table, I derive the Product Code from Product Table. Assume I name this as Product_ID_Key
My Fourth field should use Product_ID_Key conactenated with Region Code to retrieve the Mapping Description. Will the script recognize Product_ID_Key here as this has been derived only in the previous line?
I am also not sure..
Please try and let me know..
Surendra,
Thanks. I have done some thing like what you have proposed.
Applymap('Product Table',Product ID,'NA') as Product Code,
Region Code ' & ' Product Code as Mapping Code,//concatinate both fields
But, Product Code derived in the 1st line here is not getting recognized as a field when used for concatenation in Line 2. Script says the field Product Code is not found.
Yes..
Applymap('Product Table',Product ID,'NA') as Product Code
by this we can easily fetch the product code.
The problem with this
Applymap('Mapping Table',Mapping Code,'NA') as Mapping Description
In above we don't have common field/common values(key) between the our result table(Product Region) and
Mapping Table.
Hi Prasanna,
You can nest the applymap functions like so, so that you can get to the right mapping code.
Product Region(result table):
Product ID,
Region Code,
Applymap('Product Table',Product ID,'NA') as Product Code,
Applymap('Mapping Table',Region Code & Applymap('Product Table',Product ID,'NA'),'NA') as Mapping Description
Maybe that will help.