topic Re: what is the error in the expression..? in QlikView

what is the error in the expression..?

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

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,

Re: what is the error in the expression..?

YoussefBelloum — Tue, 24 Oct 2017 14:50:09 GMT

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,

Re: what is the error in the expression..?

sunny_talwar — Tue, 24 Oct 2017 14:50:50 GMT

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

Re: what is the error in the expression..?

alexandros17 — Tue, 24 Oct 2017 14:51:06 GMT

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,

Re: what is the error in the expression..?

David_Capan — Tue, 24 Oct 2017 14:56:29 GMT

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,

Re: what is the error in the expression..?

vishalgoud — Tue, 24 Oct 2017 15:58:58 GMT

Thank you all. it resolved.