topic Re: Expression help in QlikView

Expression help

danielnevitt — Wed, 25 Nov 2020 16:16:04 GMT

Hi,

I have the following expression and I would like to include where the hdr_num is DISTINCT. Is this possible?

Thanks,

Daniel

Fabs(sum(if(trade_type_name='APO' and opt_pc_name='Put',(opt_strike_price)*payment_qty, if(trade_type_name='APO' and opt_pc_name='Call',(-opt_strike_price)*payment_qty))))

Re: Expression help

marinasbc — Mon, 20 Jun 2016 15:33:01 GMT

Maybe like

Fabs(sum( {<hdr_num = {"=count(hdr_num)=1"}>}

if(trade_type_name='APO' and opt_pc_name='Put',

(opt_strike_price)*payment_qty,

if(trade_type_name='APO' and opt_pc_name='Call',

(-opt_strike_price)*payment_qty))))

Re: Expression help

danielnevitt — Mon, 20 Jun 2016 15:51:35 GMT

Thanks Marina.

Unfortunately I require distinct rather than a count where it equals 1.

Regards

Daniel

Re: Expression help

marinasbc — Mon, 20 Jun 2016 16:03:07 GMT

{<hdr_num = {"=count(distinct hdr_num)=1"}>} ?

Re: Expression help

trdandamudi — Mon, 20 Jun 2016 16:05:50 GMT

May as below:

Fabs(sum( {<hdr_num = {"=Count(DISTINCT hdr_num)"}>}

if(trade_type_name='APO' and opt_pc_name='Put', (opt_strike_price)*payment_qty,

if(trade_type_name='APO' and opt_pc_name='Call', (-opt_strike_price)*payment_qty))))

Re: Expression help

marinasbc — Mon, 20 Jun 2016 16:12:54 GMT

No, because the count(...) is just a search string so you can select the hdr_num you want in your set.

So maybe it should be where count distinct equals count:

So maybe {<hdr_num = {"=count(distinct hdr_num)=count(hdr_num)"}>} ?

Re: Expression help

danielnevitt — Mon, 20 Jun 2016 16:40:43 GMT

Thanks for your replies.

I have the below expressions that calculate the value correctly, however I am unsure how to join them together in one field:

Sum(Aggr(sum(DISTINCT{<opt_pc_name={`Put`}>}opt_strike_price*payment_qty),hdr_num))

Sum(Aggr(sum(DISTINCT{<opt_pc_name={`Call`}>}opt_strike_price*-payment_qty),hdr_num))

Regards,

Daniel

Re: Expression help

Anil_Babu_Samineni — Mon, 20 Jun 2016 17:04:12 GMT

Like Below,

Sum(Aggr(sum(DISTINCT{<opt_pc_name={`Put` & 'Call'}>}opt_strike_price*payment_qty),hdr_num))

Re: Expression help

danielnevitt — Mon, 20 Jun 2016 17:07:33 GMT

Hi Anil,

Thanks for the reply.

The Call is * -payment_qty and the Put is * payment_qty

Regards,

Daniel

Re: Expression help

Anil_Babu_Samineni — Mon, 20 Jun 2016 17:11:04 GMT

Daniel,

Can you please post sample Application for us?

Re: Expression help

tamilarasu — Mon, 20 Jun 2016 17:11:53 GMT

Daniel

You can try below expression,

If(opt_pc_name = 'Put', Sum(Aggr(sum(DISTINCT{<opt_pc_name={`Put`}>}opt_strike_price*payment_qty),hdr_num)),

Sum(Aggr(sum(DISTINCT{<opt_pc_name={`Call`}>}opt_strike_price*-payment_qty),hdr_num)) )

Re: Expression help

Anil_Babu_Samineni — Mon, 20 Jun 2016 17:16:06 GMT

Hi,

It depends what you want to achive by combining. If you need an arthemetic operation between Set 1 and 2 then can directly join them. If you want set 1 and set 2 to made for union/intersection or any other "set theory" operations, QV support them thru set analysis operators. You can read about them more in QV help (Section "Set Operators" in "set analysis")

Might be helpful meantime try with 2 variables and let me know.

- Anil

Re: Expression help

danielnevitt — Mon, 20 Jun 2016 17:20:05 GMT

Hi Tamil,

That expression seems to work. Thanks for your help.

Regards,

Daniel

Re: Expression help

tamilarasu — Mon, 20 Jun 2016 17:22:38 GMT

No Problem. Have a nice day.