Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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 ;
A simplified example below.
I just create a small "TRADES" table with the possible combinations.
I use two mapping tables MapRIMS and MapCredit to map the two parts.
If there is no match in MapRIMS, MapCredit is used in the nested ApplyMap part.
hope this helps/gg
TRADES:
LOAD * inline
[
RIMS_Trade_Condition,iCreditPassive,iCreditAggressive
,Electronic,Electronic
,Voice,Voice
,Electronic,Voice
,Voice,Electronic
EL,,
VC,,
NRR,,
IF,,
];
MapRIMS:
Mapping LOAD * inline
[
key,value
EL,Electronic
VC,Voice
NRR,No reporting required
IF,Intra firm
];
MapCredit:
Mapping LOAD * inline
[
key,value
ElectronicElectronic,Electronic
VoiceVoice,Voice
ElectronicVoice,Hybrid
VoiceElectronic,Hybrid
];
processedTRADES:
LOAD RIMS_Trade_Condition,
iCreditPassive,
iCreditAggressive,
ApplyMap('MapRIMS',RIMS_Trade_Condition, ApplyMap('MapCredit',iCreditPassive&iCreditAggressive)) As Trade_Type
Resident TRADES;
DROP Table TRADES;
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.
simplified sample data attached
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
Thanks Gandalf. I see what you mean
how can i adjust my script?
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
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'?
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 ;
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?
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.