Filter/expression in straight table chart

[#1]

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.

[nirav_bhimani]

Hi

You can try this,

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

Regards,

Nirav Bhimani

[nizamsha]

can u able to  post ur qvw

[nirav_bhimani]

Hi,

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

Regards,

Nirav Bhimani

[#5]

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..

[nirav_bhimani]

Hi,

Kindly look into following post.

Re: Blank or NULL in Set Analysis

or

Count of NULL Values

Regards,

Nirav Bhimani

[sudeepkm]

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.

[#8]

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.

[#9]

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.