topic Re: Mapping script in QlikView

Mapping script

Mon, 23 Jun 2014 14:54:15 GMT

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

maleksafa — Mon, 23 Jun 2014 15:06:00 GMT

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

Mon, 23 Jun 2014 15:14:47 GMT

simplified sample data attached

Re: Mapping script

gandalfgray — Mon, 23 Jun 2014 15:22:02 GMT

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

Re: Mapping script

Mon, 23 Jun 2014 15:43:26 GMT

Thanks Gandalf. I see what you mean

how can i adjust my script?

Re: Mapping script

gandalfgray — Tue, 24 Jun 2014 06:14:43 GMT

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

Tue, 24 Jun 2014 07:27:20 GMT

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

Tue, 24 Jun 2014 07:35:23 GMT

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

Tue, 24 Jun 2014 07:46:48 GMT

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?

Re: Mapping script

gandalfgray — Tue, 24 Jun 2014 08:02:30 GMT

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.

Re: Mapping script

Tue, 24 Jun 2014 08:05:31 GMT

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.

Re: Mapping script

gandalfgray — Tue, 24 Jun 2014 14:01:39 GMT

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

Re: Mapping script

Tue, 24 Jun 2014 14:19:04 GMT

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?

Re: Mapping script

gandalfgray — Tue, 24 Jun 2014 14:35:08 GMT

Yes

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

Re: Mapping script

Tue, 24 Jun 2014 14:52:15 GMT

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

Re: Mapping script

gandalfgray — Wed, 25 Jun 2014 07:11:54 GMT

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;

Re: Mapping script

Wed, 25 Jun 2014 07:35:19 GMT

GandalfGray, you are a genius.

thanks so much.

Re: Re: Mapping script

Wed, 25 Jun 2014 07:42:21 GMT

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

Re: Re: Mapping script

gandalfgray — Wed, 25 Jun 2014 07:45:33 GMT

It looks like you just need to do:

Left Join(TRADES)

Load *

Resident processedTRADES;

drop table processedTRADES;

Re: Re: Mapping script

Wed, 25 Jun 2014 07:53:32 GMT

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