topic Re: How to remove nulls from only fuction in QlikView

How to remove nulls from only fuction

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

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

Re: How to remove nulls from only fuction

Gysbert_Wassenaar — Wed, 28 Dec 2016 10:13:59 GMT

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

Re: How to remove nulls from only fuction

Anonymous — Wed, 28 Dec 2016 10:29:25 GMT

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

Re: How to remove nulls from only fuction

Gysbert_Wassenaar — Wed, 28 Dec 2016 10:35:09 GMT

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

Re: How to remove nulls from only fuction

Anonymous — Wed, 28 Dec 2016 10:38:30 GMT

great. it works. Thank you

Re: How to remove nulls from only fuction

Anonymous — Wed, 28 Dec 2016 11:11:41 GMT

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

Re: How to remove nulls from only fuction

Gysbert_Wassenaar — Wed, 28 Dec 2016 11:23:28 GMT

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.