Exclude Nulls

[soniasweety]

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?

[tamilarasu]

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)

[sudhirpkuwar]

Hi,

Check this

[soniasweety]

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]

[soniasweety]

sorry am using Qliksense

[tamilarasu]

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

[soniasweety]

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

[sudhirpkuwar]

Hi

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') & '%'

[soniasweety]

this is db excuting the query

select count(number) from Main_tbl

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

[tamilarasu]

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.