topic Re: IF Expression Error in QlikView

IF Expression Error

Fri, 16 Nov 2012 12:44:52 GMT

Hi,

we are trying a simple IF expression, we are not getting an error with the syntax but the results will not load.

It is simply:

if(Sum(Cases*-1>80),'80-100',if(Sum(Cases*-1>60),'60-80', 0))

We have tried numberous different expressions but cant seem to get it.

We are trying to group the values of the cases into sub groups of ranges.

The Cases value on the file is negative, thats why we are mulitplying by -1.

Thanks,

Gregg

Re: IF Expression Error

Fri, 16 Nov 2012 12:58:25 GMT

if(Sum(Cases)*-1>80,'80-100',

if(Sum(Cases)*-1>60,'60-80', 0))

use this expression

Re: IF Expression Error

Fri, 16 Nov 2012 13:01:37 GMT

Hi,

I have tried this expression before and I just tried it again to be sure but still no joy.

Thanks,

Gregg

Re: IF Expression Error

rajeshvaswani77 — Fri, 16 Nov 2012 13:05:27 GMT

Hi Gregg,

It works for me, the expression is right. Can you post an example?

thanks,

Rajesh Vaswani

Re: IF Expression Error

Fri, 16 Nov 2012 13:07:16 GMT

Hello

i tried below expr it work to me

if(sum(sal)*-2*-1>=10000,sal,

if(sum(sal)*-3*-1>=20000,sal,0))

same as above expr it works check the syntax

Re: IF Expression Error

its_anandrjs — Fri, 16 Nov 2012 13:10:12 GMT

Hi,

Use this script

if(Sum(Cases)*-1<80,'80-100',

if(Sum(Cases)*-1<60,'60-80', 0))

Because your values going negative.

Regards,

Anand

Re: IF Expression Error

Fri, 16 Nov 2012 13:52:43 GMT

Hi,

I tried it in the script also and got the attached error when loading.

I was trying it as an expression in a list box previously.

Regards,

Gregg

Re: IF Expression Error

Fri, 16 Nov 2012 14:27:18 GMT

Hi,

I tried it in the script also and got the attached error when loading.

I was trying it as an expression in a list box previously.

Regards,

Gregg

Re: IF Expression Error

MayilVahanan — Fri, 16 Nov 2012 14:32:14 GMT

Try like this

Dublin:

Load * from HeatMap.xls;

inner join(Dublin)

Load Product,

f(Sum(Cases)*-1>80,'80-100',

if(Sum(Cases)*-1>60,'60-80', 0)) as FieldName

resident Dublin group by Product;

whenever you use aggregation function like sum(),you must use group by clause .

Hope it helps

Re: IF Expression Error

Fri, 16 Nov 2012 14:50:04 GMT

Hi,

Thanks that worked.

Do you load product again to beacuse the cases are of certain products?

And the group by statement is used because we need them grouped into different categories, e.g. (80 - 100)

I just want to understand so I know for future expressions.

Thanks,

Gregg

Re: IF Expression Error

jonathandienst — Fri, 16 Nov 2012 14:56:50 GMT

Gregg

If you use an aggregation expression like Sum() in a load statement, you will need a Group By clause after the From Clause that contains all the fields in the load statement that are NOT in aggregation expressions.

Something like:

LOAD ....

Sum(....)

From Heatmap.xls (biff, .....)

Group By [Week No], Product, ....

Hope that helps

Jonathan