Populate existing field

[#1]

I need to populate an existing field Trade_Type with one of the following values

• Electronic
• Voice
• Hybrid

Two of the fields in my data are called PassiveTradeType and AggressorTradeType and i need to use the following table of logic to overwrite the Trade_Type field, which has some blank values

Passive	Aggressive	Trade_Type Outcome
Voice	Voice	Voice
Electronic	Electronic	Electronic
Voice	Electronic	Hybrid
Electronic	Voice	Hybrid

NOTE - The PassiveTradeType and AggressorTradeType fields in my data are sometime blank and I do NOT want the data in the Trade_Type field over-written in this instance, as there will be data already present.

I have attached some simplified sample data

[tresesco]

May be like:

Load

          If(Len(trim(Trade_Type))>0, If(Passive<>Aggressive, 'Hybrid', Passive)) as Trade_Type

[Anonymous]

Hi,

Something like below,

If(Len(AggressorTradeType)>0 And Len(PassiveTradeType)>0,

     If(AggressorTradeType<>PassiveTradeType, 'Hybrid', PassiveTradeType), Trade_Type) as Trade_Type

[ashfaq_haseeb]

Hi,

Look at the attached application.

Regards

ASHFAQ

[SunilChauhan]

Voice	Voice	Voice
Electronic	Electronic	Electronic
Voice	Electronic	Hybrid
Electronic	Voice	Hybrid

if(passive='Voice' and Aggresive='Voice','Voice',

if(passive='Electronic' and Aggresive='Electronic','Electronic',

if(passive='Voice' and Aggresive='Electronic','Hybrid',

if(passive='Electronic' and Aggresive='Voice','Hybrid'))))  as fieldName   in script

or in dimesion

if(passive='Voice' and Aggresive='Voice','Voice',

if(passive='Electronic' and Aggresive='Electronic','Electronic',

if(passive='Voice' and Aggresive='Electronic','Hybrid',

if(passive='Electronic' and Aggresive='Voice','Hybrid'))))

check supress null

[#6]

Thanks Satyadev.

Do i need to append this with the table I'm loading my data from? The table is called TRADES

Load

If(Len(AggressorTradeType)>0 And Len(PassiveTradeType)>0,

     If(AggressorTradeType<>PassiveTradeType, 'Hybrid', PassiveTradeType), Trade_Type) as Trade_Type

FROM TRADES;

[Anonymous]

Hi,

Yes, you have to replace your old Trade_Type field to this newly created field in your existing LOAD.

Thanks.

[Anonymous]

See attachment!

[#9]

I have looked at your file, but my script differs in that the data is not loaded from a csv, but a binary load from another qvw. Therefore I've tried doing a resident load of the existing TRADES table, this doesn't work though.

Load

If(Len(AggressorTradeType)>0 And Len(PassiveTradeType)>0,

     If(AggressorTradeType<>PassiveTradeType, 'Hybrid', PassiveTradeType), Trade_Type) as Trade_Type2

Resident TRADES;

It just seems to have assigned each Trade Type to each row of data, thus duplicating all the rows several times.