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 ;

[#2]

I need both sets of data though. If i drop one of them, will that data then not be present?

Both mapping tables to populate the same field.

[gandalfgray]

If you drop a field, the field and the data in it is gone.

Before you drop it you can use it to populate other fields.

I don't know if that is what you want to do.

It may be simpler if you here provide the set of different values you have in

RIMS_Trade_Condition,  iCreditPassive & iCreditAggressive

and what value you expect each combination to map to.

Maybe I can help then

[#4]

RIMS Trade Condition	iCredit Passive	iCredit Aggressive	Trade_Type Outcome
	Voice	Voice	Voice
	Electronic	Electronic	Electronic
	Voice	Electronic	Hybrid
	Electronic	Voice	Hybrid
EL			Electronic
VC			Voice
IF			Intra Firm
NRR			No reporting required

Is this what you mean?

[gandalfgray]

Yes

So you don't really combine RIMS Trade condition with ICredit Passive + iCredit Aggresive, right?

[#6]

Correct. In the data, if the RIMS Trade condition field is populated the ICredit Passive + iCredit Aggresive fields are ALWAYS blank.

Likewise if the ICredit Passive + iCredit Aggresive are populated, the RIMS trade condition field will ALWAYS be blank.

Also, ICredit Passive + iCredit Aggresive are either both populated or not at all

[gandalfgray · Accepted solution]

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;

[#8]

GandalfGray, you are a genius.

thanks so much.

[#9]

One last thing. How do I drop the processedTRADES table (and in the process remove the synthetic table) without losing the Trade_Type field?

See attached

[gandalfgray]

It looks like you just need to do:

Left Join(TRADES)

Load *

Resident processedTRADES;

drop table processedTRADES;

[#11]

Oddly when I add this script in, it crashes in the load at the point and I have to restart QV each time. No error messages or anything