what is the error in the expression..?

[vishalgoud]

Hi ,

expected result is not coming with the below condition.

have a list box called rm_status in which the values are like 'active' , 'Added','ACTIVE','Deleted','locked' ,'LOCKED' and 'SUSPENDED'.

want to summarise them as below

i want to status  as ACTIVE if it is ACTIVE or Active or added.- 'ACTIVE' or 'active' or 'Added' then 'ACTIVE',

in the same way want to show DELETED in the list box - 'Deleted' then 'DELETED',

'locked' or 'LOCKED' then ,'LOCKED'

if not belongs to anything then it is suspended.

if(rm_status = 'ACTIVE' or 'active' or 'Added','ACTIVE',
if(rm_status ='Deleted','DELETED',
if(rm_status ='locked' or 'LOCKED','LOCKED','SUSPENDED'))) as STATUS,

[YoussefBelloum]

Hi,

try this:

if(rm_status = 'ACTIVE' or rm_status ='active' or rm_status ='Added,ACTIVE',
if(rm_status ='Deleted,DELETED',
if(rm_status ='locked' or rm_status ='LOCKED,LOCKED,SUSPENDED'))) as STATUS,

or this:

if(match(rm_status,'ACTIVE','active', 'Added,ACTIVE'),
if(match(rm_status,'Deleted,DELETED',
if(match(rm_status,'locked', 'LOCKED','LOCKED,SUSPENDED'))) as STATUS,

[sunny_talwar · Accepted solution]

Try this

If(Match(Upper(rm_status), 'ACTIVE', 'ADDED'), 'ACTIVE',

If(Match(Upper(rm_status), 'DELETED'), 'DELETED',

If(Match(Upper(rm_status), 'LOCKED'), 'LOCKED', 'SUSPENDED'))) as STATUS

[alexandros17]

This is the right expression ....

if(rm_status = 'ACTIVE' or rm_status = 'active' or rm_status = 'Added','ACTIVE',
if(rm_status ='Deleted','DELETED',
if(rm_status ='locked' or rm_status = 'LOCKED','LOCKED','SUSPENDED'))) as STATUS,

[David_Capan]

Hello Vishal,

If you use the Upper function, you may not need a long IF statement, as Qlik will no longer read the upper and lowercase spellings as separate values.  You would still need one to include 'Added' though.

if(match(upper(rm_status),'ADDED','ACTIVE'), upper(rm_status)) as STATUS,

[vishalgoud]

Thank you all. it resolved.