topic Re: Re: Where clause in expressions in QlikView

Where clause in expressions

Fri, 07 Feb 2014 16:05:21 GMT

I want to create a chart that shows the count and sum of one field but limits the output to criteria from another field. What is the correct syntax for the expression (eg: where field1 = X). Thanks in advance

Re: Where clause in expressions

Fri, 07 Feb 2014 16:08:58 GMT

Are tables containing your fields associated?

Re: Where clause in expressions

Nicole-Smith — Fri, 07 Feb 2014 16:09:10 GMT

You should use set analysis. Something like this:

=sum({<field1={'X'}>}field2)

Re: Where clause in expressions

Fri, 07 Feb 2014 16:12:58 GMT

The fields are all derived from a single table in the load script that has no link to the other data.

Re: Where clause in expressions

juleshartley — Fri, 07 Feb 2014 16:18:19 GMT

It would help if you could give a more specific example of what you are trying to do. Are you saying that the result of an expression in one fields provides a value that you want to use as a filter on another field within another expression?

Re: Where clause in expressions

Fri, 07 Feb 2014 16:29:40 GMT

I have two dimensions on the chart Sum of field1 and Count of field1. I want to use an expression to filter the result based on a value in field2. Boyh fields are in the same table. Just to note, I am new to Qlikview.

Re: Where clause in expressions

juleshartley — Fri, 07 Feb 2014 16:35:11 GMT

I'm afraid this doesn't make sense to me. Surely if Field 2 is a dimension in the table then it is already 'filtering' the expression fields?

Re: Where clause in expressions

Nicole-Smith — Fri, 07 Feb 2014 16:38:12 GMT

=sum({<field2={'X'}>}field1)

and

=count({<field2={'X'}>}field1)

Re: Where clause in expressions

Fri, 07 Feb 2014 16:52:30 GMT

Hi Michael, You can't use Sum or Count aggregation functions in the dimensions.

Please post sample data and explain clearly what you are looking.

Re: Where clause in expressions

Fri, 07 Feb 2014 21:52:45 GMT

I want to thank you all for the responses. I will try to state the issue more clearly. I want to create a straight table chart that sums as well as counts the data in one field using crieria from another field to filter the result.

Field1 Field2

10 1

15 2

20 1

I want the sum and count of Field1 for those records that are equal to 1 in Field2. I tried =sum({<field2={'X'}>}field1) as sugested above but got unfiltered results. Again thank you.

Re: Re: Where clause in expressions

rustyfishbones — Fri, 07 Feb 2014 22:10:59 GMT

Hi Michael,

See the attached file.Does it help

Re: Where clause in expressions

agilos_mla — Fri, 07 Feb 2014 22:13:53 GMT

Are you sure that Field2 is 1 (as integer) if not I'd suggest this:

sum({<Field2={">0 <2"}>} Field1)

sum({<Field2={'1*'}>} Field1)

Re: Where clause in expressions

Fri, 07 Feb 2014 22:39:25 GMT

Thanks. This worked and helped me better understand.

Re: Where clause in expressions

Fri, 07 Feb 2014 22:42:01 GMT

Hi Michael, Please try like below:

count({<Field2={1}>} Field1)

sum({<Field2={1}>} Field1)

Re: Where clause in expressions

Fri, 07 Feb 2014 22:42:51 GMT

Thank you. This will work as well and helps me understand what I am trying to represent.