topic expression query in QlikView

expression query

Mon, 13 May 2013 13:29:35 GMT

hi all,

i have a table

id category value cat1

a abc 10 abc

a xxx 20 abc

a yyy 30 abc

a ddd 35 abc

i have a straight table and working on expression:

dimension expression:sum(value) expression:sum(value)-sum({<cat1={category}>}value)

id total total-cat1

a 95 85

third column is not giving me 95-10 as value it gives me 95 in third column as well.

please advise if syntax in 3 expression is correct?

thanks in advance

Re: expression query

Gysbert_Wassenaar — Mon, 13 May 2013 13:38:10 GMT

In this case you need to use an if statement. The set analysis expression doesn't calculate the set on a row-by-row basis. Use sum(if(cat1<>category,value)) instead. See attached qvw.

Re: expression query

er_mohit — Mon, 13 May 2013 13:51:41 GMT

See the attached file

hiii

see other file

are you looking for this

Re: expression query

Mon, 13 May 2013 14:01:35 GMT

mohit - if remove category, then does not work

Re: expression query

Mon, 13 May 2013 14:02:26 GMT

Gysbert,

the above expression also does not work ,

please advise

thanks

Re: expression query

boorgura — Mon, 13 May 2013 14:09:57 GMT

Please try the expression:

sum(if(category <> cat1, value))

Thanks,

Rocky

Re: expression query

Mon, 13 May 2013 14:12:56 GMT

same issue Gysbert in your qvw.

if you remove Category from Dimension, then 3rd column does not work

thanks

Re: expression query

Gysbert_Wassenaar — Mon, 13 May 2013 14:19:46 GMT

Huh? What are you talking about? Category isn't a dimension at all in the straight table. There is one dimension and that is id. There are two expressions. The first is sum(value) and the second is sum(if(cat1<>category,value)). The straight table shows exactly the result as you posted in your first post.

Re: expression query

Mon, 13 May 2013 14:23:16 GMT

yes Gysbert,

Category is present in raw data, but in straight table only 3 columns

1 is dimension and 2 are expression

then the formula of If function does not work

please advise

thanks

Re: expression query

boorgura — Mon, 13 May 2013 14:27:01 GMT

Dimension is id

Expressions:

1) Sum(value)

2) Sum(if(category <> cat1, value))

Re: expression query

Mon, 13 May 2013 14:30:42 GMT

hi rocky,

used the expression if(category<>cat1, sum(value),0)

still no desired result as per my post 2 table

please advise

thanks

Re: expression query

boorgura — Mon, 13 May 2013 14:32:29 GMT

Can you please attach a copy of your file?

Re: expression query

Gysbert_Wassenaar — Mon, 13 May 2013 14:39:15 GMT

Again... What are you talking about? Open the qvw I posted and take a good look at the straight table. It works just fine.

See above image. If that is not the result you want, please be clear about you do want. Because as far as I can tell this is exactly what you asked for in your first post.

Re: expression query

Mon, 13 May 2013 14:59:42 GMT

Hi Gysbert,

yes it works but just to give you additional info please find atatched image of raw data and desired data and the formula you gave does work

let me know

thanks