Create a filter using the same value

[PjK2]

Hi,

I am trying to create a filter using the same value. I tried using an if statement but it returns only two results instead of 3.

This is the code I tried:

If( Not IsNull(fail_Reference), 'fail_Reference',
If( Not IsNull(succ_Reference), 'succ_Reference',
If( Not IsNull(dis_Reference),'dis_Reference'))) as status. 

Fail_Reference and Succ_Reference appear, dis_Reference does not show. 

[menta]

i think that the value of one the field is not null but maybe blank.

You can try replace Notisnull()    with Len(Trim(fail_Reference))>0

You cn check the result witha table with the 3 dim fail_ref,succ_ref and dis_ref and check what happens when not is null dis_ref. 

[Marcel_Garcia]

Try this:

CASE
WHEN status LIKE '%fail_Reference%' THEN 'fail_Reference'
WHEN status LIKE '%succ_Reference%' THEN 'succ_Reference'
ELSE 'dis_Reference'
END AS "status"

(Like if works)

[PjK2]

Thank you for your suggestions. 

I tried both suggestions and it hasn't worked out for me. 

[tresesco]

If you are not getting the third possible status, that means your data is so, i.e. - there is no record where the first two cases fail and third becomes true. Therefore, it's about your data and not the logic. Otherwise, you might need to consider creating three separate status fields or designing a different (may be - generic key) solution.