Load table using data from already loaded table

[#1]

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?

[sarahplymale]

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:

Don't join - use Applymap instead

[surendraj]

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.

[#4]

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?

[surendraj]

I am also not sure..

Please try and let me know..

[#6]

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.

[surendraj]

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.

[sarahplymale]

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.