Mapping Issue

[#1]

Hi All,

I have a Mapping load table:

Product ID	Product Name
1	MOBILE
2	LAPTOP
3	PRINTER

My Data is like:

Product ID	Product Category
1	AAA
2	BBB
3	CCC
4	DDD
5	EEE

My Requirement is i need to map the Product Name based on the Product ID.If the Mapping table does not contain a particular Product ID then it should take Product Category as Product Name.

I am using Applymap('Mapping_load_Table_name',Product ID,Product Category).But could not succeed.

My Output should be like this:

Product ID	Product Category	Product Name
1	AAA	MOBILE
2	BBB	LAPTOP
3	CCC	PRINTER
4	DDD	DDD
5	EEE	EEE

Thanks in Advance.

Regards

Roopesh

[Anonymous]

Try putting square brackets around the field names with spaces :

    Applymap('Mapping_load_Table_name',[Product ID],[Product Category]) as MappedField

[JonnyPoole]

Here is a sample. The first load is a mapping load (and won't be a part of the data model)

The 2nd load is your data.

The 3rd data, loads your data and applies the map. When no hit is found, it uses product category.

ProductMap:

mapping load * INLINE [

    Product ID, Product Name

    1, MOBILE

    2, LAPTOP

    3, PRINTER

];

Temp:

LOAD * INLINE [

    Product ID, Product Category

    1, AAA

    2, BBB

    3, CCC

    4, DDD

    5, EEE

];

Data:

Load

  *,

  ApplyMap('ProductMap',[Product ID],[Product Category])as [Product Name]

Resident Temp;

drop table Temp;

The outcome is a single table with the desired effect.

In your situation your probably won't need the 2nd load.. Just load from your data source and add a new calculated field using applymap() as you see in the 3rd load.

[maxgro]

Mapping_load_Table_name:

mapping load * inline [

Product ID, Product Name

1, MOBILE

2, LAPTOP

3, PRINTER

];

Table:

load

  *,

  Applymap('Mapping_load_Table_name',[Product ID],[Product Category]) as [Product Name]

inline [

Product ID, Product Category

1, AAA

2, BBB

3, CCC

4, DDD

5, EEE

];