How to remove nulls from only fuction

[Anonymous]

Hi

I have 3 fields in my table id,count and flag. I am putting an expression only({<id={'1'}>}if(flag='e','1',if(flag='w','2','3'))) which will give me an output. similarly id=2 or 3 or 4 also give me the output. but when i use only({<id={'5'}>}if(flag='e','1',if(flag='w','2','3'))) it is not giving me the output. this is because when id=5 there is a null also with 'w'. in this case how can i remove null in the expression.

my intention is, i need to get output as 1 when flag equals 'e'and output=2 when flag equals 'w' and output=3 when flag not equals to 'e' or 'w'(there can be combinations with null,in that case i want to ignore null).also i need the individual view of all id's.

attaching the qvw file and source file for better understanding

sincerely

[Gysbert_Wassenaar · Accepted solution]

Like this: =only({<id={'5'},flag={'*'}>}if(flag='e','1',if(flag='w','2','3')))

[Anonymous]

Thanks gysbert. it really helps.

Please help me with more thing.

In my actual scenario if I am getting the combination with null i need to avoid null and the output should be either 1 or 2.(your expression stands good in this scenario). but when i am having only null and null combination then i need to get output as 3. is it really possible

[Gysbert_Wassenaar]

Perhaps something like this: =Alt(only({<id={'5'},flag={'*'}>}if(flag='e','1',if(flag='w','2','3'))),3)

[Anonymous]

great. it works. Thank you

[Anonymous]

thanks for your time gysbert.

I have one more doubt. i hope the alt function will work only with numerics. but what if i want to display some words instead of 1,2, and 3. in this scenario if I am using alt function always it will return the last parameter right

[Gysbert_Wassenaar]

The alt function returns the last parameter as the result if the first parameter doesn't evaluate to a number. You can use a text string as the second parameter. But if you want to use string values everywhere then you need to use something else like if-statements.