topic Re: Is there a function that returns the first non-null parameter? in QlikView

Is there a function that returns the first non-null parameter?

gussfish — Mon, 02 May 2011 03:36:41 GMT

Hi all,

is there a function that returns the first non-null parameter and has a variable number of parameters? This feels like it belongs in the Range...(...) family of functions, but I've looked through this list without finding it. So, the function I'm looking (which I'll call RangeFirst, for the sake of argument) for would, for example, return 5 if invoked as RangeFirst(null(),null(),5,6,7,4).

I'm finding I regularly write expressions like

if(isnull(expr1),expr2,expr1)

but I'd prefer to avoid the expressive clumsiness with a function like what I'm looking for, so that my example would more simply be RangeFirst(expr1,expr2).

Thanks,

Angus.

Is there a function that returns the first non-null parameter?

rwunderlich — Mon, 02 May 2011 04:29:18 GMT

For numeric values, the function is Alt().

Is there a function that returns the first non-null parameter?

gussfish — Mon, 02 May 2011 05:08:29 GMT

Hi Rob,

that's helpful to keep in mind next time I'm wanting to do this with numerics.

Unfortunately, I'm wanting at present to do this with strings 😞 . Hope sprang forth when I found WildMatch(), but was dashed on the rocks when I realised that it matches a series of wildcard expressions to a single string, whereas I'd be wanting to the opposite i.e. something like FirstMatchingString('*',str1,str2,str3...).

Any other options? Or will I need to run with the nested if()s?

Thanks,

Angus.

Is there a function that returns the first non-null parameter?

rwunderlich — Thu, 12 May 2011 20:11:21 GMT

You can build your own variable function to make the nested if solution easier. Create a variable like this in your script (or via Variable Editor)

SET AltString =

if(NOT IsNull($1), $1

,if(NOT IsNull($2), $2

,if(NOT IsNull($3), $3

,if(NOT IsNull($4), $4

, ''

)))

;

Add as many $n values as you think your max parm count.

Then use the function in script or charts like

=$(AltString(null(), 5, 'abc'))

-Rob

Re: Is there a function that returns the first non-null parameter?

Wed, 08 May 2013 08:18:18 GMT

Hi Rob,

Kindly explain how this function will work , how the parameters are substituted here ?

Thank You

Re: Is there a function that returns the first non-null parameter?

swuehl — Wed, 08 May 2013 10:13:44 GMT

qlik_techie,

this feature is called

Dollar-Sign Expansion with Parameters

Parameters can be used in variable expansions. The variable must then contain formal parameters, such as $1, $2, $3 etc. When expanding the variable, the parameters should be stated in a comma separated list.

Examples:

set MUL=’$1*$2’;

set X=$(MUL(3,7)); // returns '3*7' in X

let X=$(MUL(3,7)); // returns 21 in X

If the number of formal parameters exceeds the number of actual parameters only the formal parameters corresponding to actual parameters will be expanded. If the number of actual parameters exceeds the number of formal parameters the superfluous actual parameters will be ignored.

Examples:

set MUL=’$1*$2’;

set X=$(MUL); // returns '$1*$2' in X

set X=$(MUL(10)); // returns '10*$2' in X

let X=$(MUL(5,7,8)); // returns 35 in X

The parameter $0 returns the number of parameters actually passed by a call.

Example:

set MUL='$1*$2 $0 par';

set X=$(MUL(3,7)); // returns '3*7 2 par' in X

Re: Is there a function that returns the first non-null parameter?

swuehl — Wed, 08 May 2013 10:18:36 GMT

I think there is maybe another alternative to the OP's request:

=subfield(

concat(

valuelist(

NULL(), 1/0,'First', 'Second'

'|'),

'|',1)

This will return 'First' (first non-Null list element) if used in e.g. a text box.

I used '|' as a separator, but this can be changed to any appropriate separator (should not be part of the list elements themselfs).

Regards,

Stefan

edit:

Too bad, concat sorts its elements alphabetically. So, we would need to somehow tell QV to use the exact same order as stated in the valuelist() function arguments.

It's kind of late and this is getting messy:

=subfield(

concat(

valuelist(dual(NULL(),1), dual(1/0,2),dual('Second',3),dual( 'First',4) ),

'|',

valuelist(dual(NULL(),1), dual(1/0,2),dual('Second',3),dual( 'First',4) )

),'|',1 )