topic Re: To handle null value in QlikView

To handle null value

yogitamehta10 — Fri, 07 Jul 2017 14:22:16 GMT

Hi All,

I have a variable.

if [Dimension]>$(variablesomevalue),'Yes','No)

my issue is Everything is coming no all the null values are no here . I just want real values to compare with these variable and assign yes and no.

how I should ignore null values here.

Re: To handle null value

sunny_talwar — Fri, 07 Jul 2017 14:28:26 GMT

Are you using this as a dimension? Can you share a sample?

Re: To handle null value

yogitamehta10 — Fri, 07 Jul 2017 14:34:33 GMT

no this is just a variable i'm going to use in my expression , actually I need to count total no of yes and No.

Re: To handle null value

yogitamehta10 — Fri, 07 Jul 2017 14:35:48 GMT

Apologies I should write

if [Fieldname]>$(variablesomenumericvalue),'Yes','No')

Re: To handle null value

dan_sullivan — Fri, 07 Jul 2017 14:36:36 GMT

IF(IsNull(Dimension),Null,IF(Dimension> variable,'Yes','No'))

Then on your chart you can uncheck the include null values option

Re: To handle null value

sunny_talwar — Fri, 07 Jul 2017 14:37:54 GMT

First, is there a typo here where you are not using a parenthesis after if?

If([Fieldname]....)

Second, I am not sure I understand the complete scenario. Please provide information related to how you define your variable. Where you plan to use this expression? What are the dimensions? etc etc...

Re: To handle null value

yogitamehta10 — Fri, 07 Jul 2017 14:56:20 GMT

this variable will give me number of yes and No.

and in expression I have to say

count($(variable)='Yes',ID)/Count(($(variable)='Yes','No',ID)

as now in No it is including all the blank fields in sheet.

Re: To handle null value

sunny_talwar — Fri, 07 Jul 2017 15:00:01 GMT

Some sample data to show how the data looks like?

Re: To handle null value

yogitamehta10 — Fri, 07 Jul 2017 15:06:45 GMT

ID Fieldname (numericvalue)

1 4

2 7

3 8

5 9

I want to say if values in field number is greater then 4 then put yes and if not then put No and ignore blank values

then I need to calculate %

Count (numberof Yes)/count(number of yes and No)

Re: To handle null value

sunny_talwar — Fri, 07 Jul 2017 15:10:06 GMT

May be try this

Count(DISTINCT {<ID = {"=Fieldname > 4 and Len(Trim(Fieldname)) > 0"}>} ID)/Count(DISTINCT {<ID = {"=Len(Trim(Fieldname)) > 0"}>} ID)

Re: To handle null value

yogitamehta10 — Fri, 07 Jul 2017 15:23:32 GMT

I can usein expression to check null values because I have another step before that.

ID Fieldname (numericvalue) fieldname2

1 4 yes

2 7 No

3 8 yes

4 NO

5 9

so I should have two variables one variable is for field 1 and another is field 2 then there is third variable which says if both values are yes in both the fields then is should be yes , otherwise No. so need to figure out null values at variable level. then need to compare values of both the fields and then in expression I need to calculate %.

Re: To handle null value

yogitamehta10 — Mon, 10 Jul 2017 08:13:55 GMT

any Help ?

Re: To handle null value

sunny_talwar — Mon, 10 Jul 2017 08:46:06 GMT

I wish to, but I am really not sure I understand what you have and what you are looking to do.

Re: To handle null value

yogitamehta10 — Mon, 10 Jul 2017 09:01:05 GMT

HI sunny,

Thanks for your reply

my first step is to convert numbers in to Yes and No

so I have suppose two fields

ID value

1 4

3 8

4 9

5 10

6 21

so instead of number I want yes no cant use in main expression as I need to compare this yes and no with other fields then will calculate %

no now I have to write a if condition.

if (value>4, 'YES', 'No')

but the problem is when I check the count of yes it says 4 that is right but when I say count of no it says 2. but actually it should be 1.

I hope i'm clear.

Re: To handle null value

sunny_talwar — Mon, 10 Jul 2017 09:06:51 GMT

I guess try this

If(value > 4, 'YES',

If(value <= 4 and Len(Trim(value)) > 0, 'No'))

Re: To handle null value

yogitamehta10 — Mon, 10 Jul 2017 09:41:31 GMT

so now I got Yes and No in field likewise in I have created another expression for different field and got the yes no value , now I need to compare values of there two fields and say if both the value are yes then yes otherwise No.

Re: To handle null value

sunny_talwar — Mon, 10 Jul 2017 11:05:38 GMT

May be like this

If(Expression1 = 'YES' and Expression2 = 'YES', 'YES', 'NO')