Skip to main content
Announcements
Introducing Qlik Answers: A plug-and-play, Generative AI powered RAG solution. READ ALL ABOUT IT!
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Filter/expression in straight table chart

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.

8 Replies
nirav_bhimani
Partner - Specialist
Partner - Specialist

Hi

You can try this,

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

Regards,

Nirav Bhimani

nizamsha
Specialist II
Specialist II

can u able to  post ur qvw

nirav_bhimani
Partner - Specialist
Partner - Specialist

Hi,

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

Regards,

Nirav Bhimani

Not applicable
Author

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
Partner - Specialist
Partner - Specialist

Hi,

Kindly look into following post.

Re: Blank or NULL in Set Analysis

or

Count of NULL Values

Regards,

Nirav Bhimani

sudeepkm
Specialist III
Specialist III

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.

Not applicable
Author

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.

Not applicable
Author

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.