Set Analysis not removing values

[#1]

Hi,

I'm quite new to Qlikview and trying to do Set Analysis on a report atm. I've the below code in an expression on a straight table. It counts the values and 1 is appearing next to every row that has a Status of C in it, but it's not removing the ones that don't have a status of C. Should it be, or do I need to do something else? Again, complete beginner really, and haven't had much training, so apologies if it's a stupid question.

=count(distinct{<STATUS = {"C"} >} [POLICY_NO] )

[#2]

Yup.

[ashfaq_haseeb]

Hi,

did you try my suggestion.

=count(distinct{<STATUS = {'C'} >} [POLICY_NO] )

make sure C you use is in capital letter if you have space then try below

=count(distinct{<trim(STATUS) = {'C'} >} [POLICY_NO] )

Let me know if that  worked.

Regards

ASHFAQ

[#4]

That works, just having C and Not C instead of 1 and 0, but it doesn't remove them from the table.

Atm, on the above expression, it prints 1 if C, and 0 if not C, but all the 0s are still there. I need to have them removed, without using filters. Preferably in the expression, which is why I'm trying Set Analysis. I can remove them in the script, but I'd rather not, as I'll need 3 seperate Straight Tables at the end, just using different statuses instead of C.

[#5]

On the trim formula, I get an error on {'C'} saying Error in Set Modifier Expression. I know from the SQL code that we have that there's no space next to the C anyway.

The rest of it made no difference. It just did the same and printed back 1 and 0

[#6 · Accepted solution]

suppress nulls and use

If(Status = 'C', <Dimension_Name>)

[simenkg]

You say that you have 20 other expressions.

When the expression is evaluated it will only give you a value for STATUS = {"C"} , but the rows still exists on the other 20 expressions. To make the rows dissappear, this condition has to be added to all the other expressions.

if the expressions now is: [Field] then replace it with only({<Status={C}>}[Field])

Do this for all expressions and the rows should dissappear.

[#8]

This worked. Thank you.

[#9]

Ya, I just discovered that if I removed the other expressions, it would work, so this would more than likely have worked also. Thanks

[simenkg]

Just a word of caution on the use of calculated dimensions. They are extremely slow. This will have no effect if your model contains a small amount of data, but if there is any sort of size to the data it will quickly grind to a halt. The use of set analysis is much (MUCH!) faster, and is the best practice for dealing with these cases.