21 Replies Latest reply: Jun 25, 2014 4:08 AM by Alex Hamilton McLeod RSS

    Mapping script

      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 ;

        • Re: Mapping script
          Malek Safa

          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.

            • Re: Re: Mapping script

              simplified sample data attached

                • Re: Mapping script
                  Goran Korsgren

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

                   

                  key Trade_Type
                  ElectronicVoiceHybrid
                  ELElectronicElectronicElectronic
                  IFIntra firm
                  VCVoiceVoiceVoice
                  VoiceElectronicHybrid

                   

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

                   

                  /gg

                    • Re: Mapping script

                      Thanks Gandalf. I see what you mean

                       

                      how can i adjust my script?

                        • Re: Mapping script
                          Goran Korsgren

                          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

                            • Re: Mapping script

                              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'?

                              • Re: Re: Mapping script

                                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 ;

                                • Re: Mapping script

                                  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?