topic Re: How can i create expression with if command? in QlikView

How can i create expression with if command?

Tue, 07 Oct 2014 04:56:39 GMT

Hi,

Please help me to create the following expression in my project, even expression shows the status OK, it does not woks.

SUM(IF('BRO_COMM'=0 AND 'AGE_COM'=0 AND 'GRO_COM'=0,(COM_PRE*ME_COM+SR*ME_RS+TC*ME_TC)/100))

regard,

Priyantha.

Re: How can i create expression with if command?

jsingh71 — Tue, 07 Oct 2014 05:01:43 GMT

Try this:

(IF('BRO_COMM'=0 AND 'AGE_COM'=0 AND 'GRO_COM'=0,((COM_PRE*ME_COM)+(SR*ME_RS)+(TC*ME_TC))/100))

Re: How can i create expression with if command?

jagan — Tue, 07 Oct 2014 05:03:51 GMT

Hi,

Try this

=SUM({<BRO_COMM={'0'}, AGE_COM={'0'}, GRO_COM={'0'}>} ((COM_PRE*ME_COM)+(SR*ME_RS)+(TC*ME_TC)))/100

Hope this helps you.

Regards,

Jagan.

Re: How can i create expression with if command?

Tue, 07 Oct 2014 05:26:35 GMT

Dear Priyantaha,

Try this...

if('BRO_COMM'=0 AND 'AGE_COM'=0 AND 'GRO_COM'=0,sum((COM_PRE*ME_COM+SR*ME_RS+TC*ME_TC))/100)

Thanks & Regards

Prince Anand

Re: How can i create expression with if command?

Tue, 07 Oct 2014 07:59:39 GMT

Dear Jagan,

Thaks for the reply.

the formula you sent is worked, but ÏF" condition dose not works. it means, even above one of three expressions have values grater than "0" , formula result shows value (in this case result should be zero)

Regards,

Priyantha.

Re: How can i create expression with if command?

jyothish8807 — Tue, 07 Oct 2014 08:06:34 GMT

Hi Priyanth,

Try using ALT function in the above expression suggested by Jagan.

=Alt(SUM({<BRO_COMM={'0'}, AGE_COM={'0'}, GRO_COM={'0'}>} ((COM_PRE*ME_COM)+(SR*ME_RS)+(TC*ME_TC)))/100,0)

Regards

Re: How can i create expression with if command?

Tue, 07 Oct 2014 08:08:10 GMT

Try below:

SUM(IF('BRO_COMM'='0' AND 'AGE_COM'='0' AND 'GRO_COM'='0',(((COM_PRE*ME_COM)+(SR*ME_RS)+(TC*ME_TC))/100),0))

I assume the 'BRO_COMM', 'AGE_COM' are not fields but dynamic values coming from a variable.

Thanks,

Singh

Re: How can i create expression with if command?

Tue, 07 Oct 2014 08:18:46 GMT

Dear Kc,

Thaks for the reply.

Regards,

Priyantha.

Re: How can i create expression with if command?

jagan — Tue, 07 Oct 2014 08:27:20 GMT

Hi,

Can you attach some sample data and explain with an example and expected output?

Regards,

Jagan.

Re: How can i create expression with if command?

Tue, 07 Oct 2014 09:39:20 GMT


BCOD	CRE_CODE	REF_NO	PREMUM	BRO_COMM	BRO_ORC	AGE_COM	AGE_ORC	GRO_COM	GRO_ORC	ME_COM	TOT_COM	DIFF
BA00	2019-027	BA0014D0028271	43584	5241	383	0	0	0	0	0	5241	0
		BA0014D0028276	110605	13479	994	0	0	0	0	0	13479	0
		BA0014D0028346	64483	7885	583	0	0	0	0	0	7885	0
		Total	218672	26604	1959	0	0	0	0	0	26604	0
	C0001084	BA0014D0028368	48859	0	0	4261	915	0	0	0	4261	0
		Total	48859	0	0	4261	915	0	0	0	4261	0
	C0001731	BA0014D0028226	24974	0	0	2217	471	0	0	0	2217	0
		BA0014D0028309	21560	0	0	2000	416	0	0	0	2000	0
		BA0014D0028425	7040	0	0	1000	146	0	0	0	648	0
		BA0014D0028464	15932	0	0	2219	336	0	0	0	2134	0
		BA0014D0028465	6765	0	0	953	141	0	0	0	923	0
		BA0014D0028480	25625	0	0	3606	536	0	0	0	2325	0
		Total	101896	0	0	11996	2047	0	0	0	10247	0
	C0004862	BA0014D0028209	45500	0	392	0	847	5005	392	0	5005	0
		Total	45500	0	392	0	847	5005	392	0	5005	0
	C0004866	BA0014D0028249	13660	0	122	0	258	1503	122	0	1503	0
		BA0014D0028283	17953	0	157	0	337	1975	157	0	1975	0
		BA0014D0028328	6001	0	56	0	116	660	56	0	660	0

Dear Jagan,

Thanks for the quick reply.

See the above table. i want to get the output in the column 'ME_COM'. If 'BRO_COMM' , 'AGE_COM' and 'GRO_COM' columns available zero value then only the 'ME_COM' calculated('PREMIUM'*5%). in other word, if some value available in one of above three columns(grater than zero) the 'ME_COM' result should be zero.

Regards,

Priyantha

Re: How can i create expression with if command?

jagan — Wed, 08 Oct 2014 01:07:53 GMT

Hi,

Try this expression in front end

=Alt(Sum({<BRO_COMM={0}, AGE_COM = {<0>}, GRO_COM = {0}>} PREMIUM) *0.05, 0)

If you want to do this calculation in script then try this

LOAD

If(BRO_COMM=0 AND AGE_COM=0 AND GRO_COM = 0, PREMIUM * 0.05, 0) AS ME_COM

FROM DataSource;

Hope this helps you.

Regards,

Jagan.

Re: How can i create expression with if command?

Anonymous — Wed, 08 Oct 2014 03:44:38 GMT

Try this:

IF((BRO_COMM=0 AND AGE_COM=0 AND GRO_COM=0),((COM_PRE*ME_COM)+(SR*ME_RS)+(TC*ME_TC))/100,0)

or you may try below

if (BRO_COMM=0,if(AGE_COM=0,if(GRO_COM=0,((COM_PRE*ME_COM)+(SR*ME_RS)+(TC*ME_TC))/100,0)))

Mark the Question as answered, if any one's answer is works for you.

Regards,

Chinna