topic If Statement and Null Value Problems in QlikView

If Statement and Null Value Problems

Thu, 28 Jul 2011 10:09:26 GMT

Hi guys, I have a small issue with an If statement and null values.

I have a range of values which are banded up based on another field.

The original field contains numbers between 0 and 15. The requirement is to include only numbers between 0 and 10 and exclude all numbers above 10.

Example

IF([Value 1] > 0 and [Value 1] < 5, '< 5',

IF([Value 1] >= 5 and [Value 1] <10, >5')) as New_Value.

I have no else part in my If statement.

When I use my new field called New_Value in a table, I try to calculate percentages and I find out that my percentages don't sum up to 100% when I use only <5 and >5, because there are some null values which correspond to values above 10 that I have excluded in my load script.

Is there a way I can get rid of all the unwanted values above 10, so that when i work out percentages in my table, my totals will sum up to 100% for only values between 0 and 10?

Thank you

If Statement and Null Value Problems

jonathandienst — Thu, 28 Jul 2011 10:52:20 GMT

You can get the total using

Sum({<[Value 1] = {">=0 <=10"}>} [Value 1])

Then you can calculate the percentages only for values 0 - 10).

Hope that helps

Jonathan

If Statement and Null Value Problems

Thu, 28 Jul 2011 11:03:50 GMT

Hi Jonathan,

Thanks for your reply. Your suggestion would be fine if I had only one dimension, but I am using a cycle group in the table with multiple dimensions, so putting set analysis specific to one dimension would make the evaluation of other dimensions to be incorrect.

Any othr suggestions?

If Statement and Null Value Problems

erichshiino — Fri, 29 Jul 2011 04:28:40 GMT

Hi,

You can create a variable ( for example vDim) with the following expression (in setting->Variable overview)

=GetCurrentField([New Group]) -----> Where [New Group] is the name of the group you are using

(including the equal sign, ok?)

Then, the set analysis will be:

Sum({<[$(vDim)] = {">=0 <=10"}>}[$(vDim)])

Hope this helps,

Erich