Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Not applicable

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 ;

1 Solution

Accepted Solutions
gandalfgray
Valued Contributor

Re: Mapping script

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;

21 Replies
maleksafa
Valued Contributor

Re: Mapping script

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.

Not applicable

Re: Re: Mapping script

simplified sample data attached

gandalfgray
Valued Contributor

Re: Mapping script

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

Not applicable

Re: Mapping script

Thanks Gandalf. I see what you mean

how can i adjust my script?

gandalfgray
Valued Contributor

Re: Mapping 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

Not applicable

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

Not applicable

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 ;

Not applicable

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?

gandalfgray
Valued Contributor

Re: Mapping script

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.

Community Browser