Normalising mapping table

[Anonymous]

Hi I have the following mapping table 'SALES_MAPPING' which I am mapping to my main table SALES.. I want to normalise some of the SALES_PERSON values so the names appear as in the per the 'normalised' tab. ( Effectively I am just reducing them to single names, without surnames, initials etc ).

I am using a standard mapping load statement to load in the mapping table.

Where would I write the modifications to normalise the values - would it be in the mapping load statement or in the applymap statement and if so what should it look like ?

Thanks

[sunny_talwar]

So the above is your Mapping Table? You can do this

MappingTable:

Mapping

LOAD SALES_ID,

     SubField(SALES_PERSON, '.', 1) as SALES_PERSON

FROM ....;

and then use ApplyMap as you would....

[RonaldDoes]

Hi Shibli,

Since Normalised also contains the SALES_ID, why don't you just map the Normalised table to your data?

[prieper]

mapNames:

LOAD Sales_ID, SALESPERSON FROM Sales_Mapping.xlsx (ooxml, embedded labels, table is [Normalized]);

Data:

LOAD

*,

APPLYMAP('mapNames', Sales_ID)          AS SALESPERSON_NORMALIZED

FROM .....;

[Anonymous]

Sorry should have clarified the normalised tab was my expected outputted values of the original mapping. For my real life example the mapping table may be updated so I need to script to pick up these changes.

[prieper]

SUBFIELD(SALESPERSON, '.', 1)          AS FirstName

should do?