topic Re: Filter/expression in straight table chart in QlikView

Filter/expression in straight table chart

Fri, 02 May 2014 10:13:13 GMT

Hi guys,

I was wondering if you could shed some light on this problem I have. I have a straight table chart with a bunch of fields and I've used a filter in the expressions to filter:

=if(Status <> 'Closed' AND Status <> 'Resolved' AND RequestType <> 'Work Order' AND RequestType <> 'Project Request' AND IsNull(Technician) ,[Request ID])

This is all working fine. But the issue is the count of this table and the actual number of rows seem to be different.

In the table's title, I have the following to get the count of rows:

count(if(Status <> 'Closed' AND Status <> 'Resolved' AND RequestType <> 'Work Order' AND RequestType <> 'Project Request' AND IsNull(Technician),[Request ID]))

This is showing 14293 BUT when I export the table to excel there's only 42 rows (this is the correct number). I can't figure out why it's giving me 2 differnt numbers when I am using the same filter in both expressions?

What am I doing wrong? Is it something to do with the null values?

thanks

Cameron.

Re: Filter/expression in straight table chart

nirav_bhimani — Fri, 02 May 2014 10:23:39 GMT

You can try this,

Count ({<Status -={"Closed","Resolved"}, RequestType -={"Work Order","Project Request"}, Technician-={'*'}>} Distinct Request ID)

Regards,

Nirav Bhimani

Re: Filter/expression in straight table chart

nizamsha — Fri, 02 May 2014 10:24:16 GMT

can u able to post ur qvw

Re: Filter/expression in straight table chart

nirav_bhimani — Fri, 02 May 2014 10:32:24 GMT

Hi,

I dont have any Qvw for that, based on your requirement I have created the expression.

Regards,

Nirav Bhimani

Re: Filter/expression in straight table chart

Fri, 02 May 2014 10:53:13 GMT

Hi Nirav,

Thanks for the reply. When I pasted that in to the expression box I got a red underline. But I clicked OK and getting a value of 0.

Shouldn't the strings be encosed by single quotes instead of double quotes? 'Closed' instead of "Closed"? Even that didn't work though.

I am certain that is something to do with null values as when I replaced the nulls in the SQL select command and then modify the expression, I get the correct number of values.

But I don't understand how the table displays the correct number of rows using the original expression but when counting it gives a different value..

Re: Filter/expression in straight table chart

nirav_bhimani — Fri, 02 May 2014 11:09:41 GMT

Hi,

Kindly look into following post.

Re: Blank or NULL in Set Analysis

Count of NULL Values

Regards,

Nirav Bhimani

Re: Filter/expression in straight table chart

sudeepkm — Fri, 02 May 2014 11:23:58 GMT

can you please post your qvw? you mentioned that in excel it is giving you 42 records however when you load the table into QlikView it is giving 14K.

Can you create another qvw just loading data from the excel (single table). It may happen that due to association with other tables in QlikView the count may be different.

Re: Filter/expression in straight table chart

Fri, 02 May 2014 11:52:42 GMT

Hi Sudeep, It's a large file (25MB).

No I load the data from a .QVD file (which gets it's data from another QVW file which selects data using a SQL select statement).

In the first QVW file when I use isnull() in the SQL statment to replace nulls with 'NA' and then later use 'NA' in my second QVW file when filtering, I get the correct count displayed.

Re: Filter/expression in straight table chart

Fri, 02 May 2014 12:01:29 GMT

I don't know why but when I use the following instead of the original count expression, I get the correct number of rows:

sum(if(Status <> 'Closed' AND Status <> 'Resolved' AND RequestType <> 'Work Order' AND RequestType <> 'Project Request' AND IsNull(Technician),1))

Thanks everyone for your input.