topic Re: Exclude Nulls in QlikView

Exclude Nulls

soniasweety — Wed, 25 Nov 2020 16:16:04 GMT

Hi all,

I have a column Status

in that values are like below

Status

success

"success with no issue "

Failed

Unsuccessfull

so here my requirement is : Caluclate the Failed %

logic : if(Status='successful' or Status='success With No Issues' or close_code='successful_issues', 'Yes','No') as StatusFlag,

Front end :

count({<StatusFlag={'No'}>}Number)

/count(Number)

its working but in data I have null values if I take flag value "No" except success null also taking into consideration so I want to exclude that how to do?

Re: Exclude Nulls

tamilarasu — Mon, 30 Apr 2018 06:03:18 GMT

Hello,

count({<Status={'*'}, StatusFlag={'No'}>}Number)

/count(Number)

If you want to exclude the null status in denominator as well then change it to count({<Status={'*'}>}Number)

Re: Exclude Nulls

sudhirpkuwar — Mon, 30 Apr 2018 06:06:13 GMT

Hi,

Check this

Re: Exclude Nulls

soniasweety — Mon, 30 Apr 2018 06:11:17 GMT

instead of in front end expression

how to write in if condition only [means instead of success how can I write exclude null and failed and unsuccessfull]

Re: Exclude Nulls

soniasweety — Mon, 30 Apr 2018 06:11:49 GMT

sorry am using Qliksense

Re: Exclude Nulls

tamilarasu — Mon, 30 Apr 2018 06:17:41 GMT

In that case, can we leave status_flag for null values as Blank? Something like below

If(Isnull(Status),'', If(Match(Status,'successful','success With No Issues') or close_code='successful_issues', 'Yes','No')) as StatusFlag

Re: Exclude Nulls

soniasweety — Mon, 30 Apr 2018 06:20:35 GMT

sorry tamil

close_code is nothing but status only below is original expression

logic : if(Status='successful' or Status='success With No Issues' or Status='successful_issues', 'Yes','No') as StatusFlag

Re: Exclude Nulls

sudhirpkuwar — Mon, 30 Apr 2018 06:22:46 GMT

At Back End:

D1:

LOAD * INLINE [

Status

Success

Success with no issues

Failed

Unsuccessfull

];

Load *,

if(Status='Success' or Status='Success with no issues', 'Yes','No') as StatusFlag

Resident D1;

Drop Table D1;

EXIT SCRIPT;

Front End:

=Num(Count({<StatusFlag={"No"}>}StatusFlag)

Count(StatusFlag)*100,'##.00') & '%'

Re: Exclude Nulls

soniasweety — Mon, 30 Apr 2018 06:23:22 GMT

this is db excuting the query

select count(number) from Main_tbl

where Status = 'Failed ' or Status = 'Unsuccessful' or status = 'Not succeed'

Re: Exclude Nulls

tamilarasu — Mon, 30 Apr 2018 06:24:15 GMT

Don't be sorry, Sony . You can change your expression something like below.

If(Not IsNull(Status), If(Match(Status,'successful','success With No Issues', 'successful_issues') , 'Yes','No')) as StatusFlag

The above if condition will leave null status value flag (StatusFlag) as null.

Re: Exclude Nulls

sudhirpkuwar — Mon, 30 Apr 2018 06:38:48 GMT

You just need the Failed % right....?

Re: Exclude Nulls

singbittoo — Mon, 30 Apr 2018 06:52:53 GMT

tamil's expression will work in front end, for script level

you can write

** the below one is also tamil's code only just the '' i replaced with 'null'

If(Isnull(Status),'null', If(Match(Status,'successful','success With No Issues','successful_issues') , 'Yes','No')) as StatusFlag

and in your older expression will work fine

Re: Exclude Nulls

soniasweety — Mon, 30 Apr 2018 12:10:20 GMT

yes sudhir