topic Re: Populate existing field in QlikView

Populate existing field

Tue, 24 Jun 2014 08:23:03 GMT

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

Re: Populate existing field

tresB — Tue, 24 Jun 2014 08:29:43 GMT

May be like:

Load

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

Re: Populate existing field

Anonymous — Tue, 24 Jun 2014 08:36:06 GMT

Hi,

Something like below,

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

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

Re: Populate existing field

ashfaq_haseeb — Tue, 24 Jun 2014 08:49:25 GMT

Hi,

Look at the attached application.

Regards

ASHFAQ

Re: Populate existing field

SunilChauhan — Tue, 24 Jun 2014 08:58:42 GMT

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

Re: Populate existing field

Tue, 24 Jun 2014 10:12:46 GMT

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;

Re: Populate existing field

Anonymous — Tue, 24 Jun 2014 10:25:21 GMT

Hi,

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

Thanks.

Re: Re: Populate existing field

Anonymous — Tue, 24 Jun 2014 10:28:06 GMT

See attachment!

Re: Re: Populate existing field

Tue, 24 Jun 2014 13:58:18 GMT

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.