Mapping script

[#1]

The following mapping script produces no errors when I load it, but at the same time map none of the values to the Trade_Type field I am creating.

Any ideas?

Rename table TRADES to Trade_Temp;

Trade_Conditions:

LOAD * inline

[

RIMS_Trade_Condition,iCreditPassive,iCreditAggressive,Trade_Type

EL,Electronic,Electronic,Electronic

VC,Voice,Voice,Voice

,Electronic,Voice,Hybrid

,Voice,Electronic,Hybrid

IF,,Intra firm

];

MapTradeCondition:

Mapping LOAD

RIMS_Trade_Condition & iCreditPassive & iCreditAggressive as key ,

     Trade_Type

Resident Trade_Conditions;

TRADES:

LOAD

*,

ApplyMap('MapTradeCondition',PassiveTradeType,ApplyMap('MapTradeCondition',AggressorTradeType,ApplyMap('MapTradeCondition',TradeCondition))) as Trade_Type

Resident Trade_Temp;

Drop Tables  Trade_Temp,Trade_Conditions ;

[maleksafa]

can you attach a sample document, it might be due to special characters or spaces in the field, or even the key fields in the mapping are not correct.

[#3]

simplified sample data attached

[gandalfgray]

Based on your script the created mapping table looks like this:

key	Trade_Type
ElectronicVoice	Hybrid
ELElectronicElectronic	Electronic
IFIntra firm
VCVoiceVoice	Voice
VoiceElectronic	Hybrid

I don't think you have any TradeCondition values that matches those keys

/gg

[#5]

Thanks Gandalf. I see what you mean

how can i adjust my script?

[gandalfgray]

Hi Alex

I suggest you start by doing what you are trying to do step by step so you can see you get the expected result after each step.

Probably it is better to create three different mapping tables, one for "TradeCondition", one for "AggressorTradeType" and one for "PassiveTradeType", so your nested ApplyMap statement would look something like.

ApplyMap('MapPassiveCondition',PassiveTradeType,

   ApplyMap('MapAggressorCondition',AggressorTradeType,

      ApplyMap('MapTradeCondition',TradeCondition))) as Trade_Type

If you don't get expected result, add another field:

ApplyMap('MapTradeCondition',TradeCondition) as Trade_Type_check1

and see what values you get in the Trade_type_check1 field

[#7]

Would this step by step approach work, if the PassiveTradeType and AggressorTradeType depend on each other in the instance where one of them is 'electronic' and the other is 'voice' and the outcome needs to be 'Hybrid'?

[#8]

I tried the script below, but i get the error; "Field names must be unique within table". (See attached). I'm not sure which fields this erroris referring to though

Rename table TRADES to Trade_Temp;

RIMS_Trade_Conditions:

LOAD * inline

[

RIMS_Trade_Condition, Trade_Type

EL,Electronic

IF,Intra Firm

VC,Voice

];

MapTradeCondition:

Mapping LOAD

RIMS_Trade_Condition ,

    Trade_Type

Resident RIMS_Trade_Conditions;

TRADES:

LOAD

*,

ApplyMap('MapTradeCondition',TradeCondition,) as Trade_Type

Resident Trade_Temp;

Drop Tables  Trade_Temp,RIMS_Trade_Conditions ;

Rename table TRADES to Trade_Temp;

Trade_Conditions:

LOAD * inline

[

iCreditPassive,iCreditAggressive,Trade_Type

Electronic,Electronic,Electronic

Voice,Voice,Voice

Electronic,Voice,Hybrid

Voice,Electronic,Hybrid

];

MapTradeCondition:

Mapping LOAD

iCreditPassive & iCreditAggressive as key ,

    Trade_Type

Resident Trade_Conditions;

TRADES:

LOAD

*,

ApplyMap('MapTradeCondition',PassiveTradeType,ApplyMap('MapTradeCondition',AggressorTradeType,)) as Trade_Type

Resident Trade_Temp;

Drop Tables  Trade_Temp,Trade_Conditions ;

[#9]

Ah, i realise I see it is because I am trying to overwrite the Trade_Type field created from the previous mapping.

Is it possible in teh 2nd mapping to populate the Trade_Type field created from the first mapping?

[gandalfgray]

about overwriting Trade_Type, you need to give them different names, ie the first instance you call Trade_Type1, and the second Trade_Type2 etc.

Then you can drop the one(s) you dont need in the end.

You have also given the same name (MapTradeCondition) to the mapping tables.

Change that so they have unique names.