topic Re: Set Analysis not removing values in QlikView

Set Analysis not removing values

Wed, 15 Oct 2014 10:33:18 GMT

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] )

Re: Set Analysis not removing values

simondachstr — Wed, 15 Oct 2014 10:36:39 GMT

In rows where Status is not C, do you get null or a numeric value as a result?

Re: Set Analysis not removing values

juleshartley — Wed, 15 Oct 2014 10:36:56 GMT

Is [Policy_No] your only dimension in the table?

Re: Set Analysis not removing values

Wed, 15 Oct 2014 10:39:59 GMT

I get 0 in the cell when it's not C

Re: Set Analysis not removing values

Wed, 15 Oct 2014 10:41:03 GMT

I have Policy No, Status and Main_Mem_No. Then there's about 20 expressions that could be dimensions but I want a horizontal scroll bar in the table.

Re: Set Analysis not removing values

ashfaq_haseeb — Wed, 15 Oct 2014 10:45:01 GMT

hi,

try like this

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

Regards

ASHFAQ

Re: Set Analysis not removing values

juleshartley — Wed, 15 Oct 2014 10:45:57 GMT

Have you ticked 'suppress zero values' in the 'presentation' tab?

Re: Set Analysis not removing values

annafuksa1 — Wed, 15 Oct 2014 10:46:23 GMT

Can you send your app ?

Re: Set Analysis not removing values

Wed, 15 Oct 2014 10:47:25 GMT

Your Syntax is correct, the only thing that you might what to check is the field name and value are Case and Space sensitive.

What is your dimension?

Alternatively you can also try the following as your dimension:

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

Regards

Re: Set Analysis not removing values

Wed, 15 Oct 2014 10:51:24 GMT

Nope. Work and confidential information in it.

Re: Set Analysis not removing values

Wed, 15 Oct 2014 10:51:38 GMT

Yup.

Re: Set Analysis not removing values

ashfaq_haseeb — Wed, 15 Oct 2014 10:54:31 GMT

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

Re: Set Analysis not removing values

Wed, 15 Oct 2014 10:57:57 GMT

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.

Re: Set Analysis not removing values

Wed, 15 Oct 2014 11:02:20 GMT

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

Re: Set Analysis not removing values

Wed, 15 Oct 2014 11:03:36 GMT

suppress nulls and use

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

Re: Set Analysis not removing values

simenkg — Wed, 15 Oct 2014 11:09:42 GMT

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.

Re: Set Analysis not removing values

Wed, 15 Oct 2014 11:10:31 GMT

This worked. Thank you.

Re: Set Analysis not removing values

Wed, 15 Oct 2014 11:11:20 GMT

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

Re: Set Analysis not removing values

simenkg — Wed, 15 Oct 2014 11:17:08 GMT

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.