Null check the result of a variable

[Evan0211]

I have a variable that formats a string in a particular manner and returns the string. There are instances where this will return a null, which is by design. However, instead of it returning - (the qlik representation of null), I would like to to return a string 'Empty'.

Variable is vConcat_String

if I do: LEN($(vConcat_String)) I get the length of the strings returned if there is a string to return, but on null, I still get '-'. 

I have tried wrapping it in an isnull() function like: =if(isnull($(vConcat_String)), 'Empty', $(vConcat_String)) and it continues to express null values to '-' in the table.  I have also tried LEN: if(LEN($(vConcat_String)>1, $(vConcat_String), 'Empty') but the null value still expresses to '-' instead of 'Empty'.

Any suggestions?

[Vegar]

Take a look at coalesce() or alt().

Coalesce(LEN($(vConcat_String)), 'Empty')

Alt(LEN($(vConcat_String)), 'Empty')

[Evan0211]

Both of those yield the same result, it shows '-' instead of 'Empty'

[Vegar]

What's vConcat_String is it a string? An expression? Field name?

Depending on what the $(vConcat_String) returns, you might need to quote the result like this:

LEN('$(vConcat_String)')

[Evan0211]

It is an if statement that checks three parameters and returns a string.

I have tried taking it out of the variable and checking the if statement using LEN and isnull() and no matter what, it returns null instead of 'Empty'

I have even gone as far as breaking down the if statement into segments and running just the segments to try and debug it and it continues to return a null instead of 'Empty'

if(not isnull(V_PII) and not isnull(C_NO), V_PII, 
if(isnull(V_PII) and not isnull(C_NO), C_NO, 
if(not isnull(V_PII) and isnull(C_NO) and isnull(T_O), V_PII, 
if(not isnull(V_PII) and isnull(C_NO) and not isnull(T_O), V_PII &'-'& T_O))))

This is the if statement that the variable is made from 

[marcus_sommer]

You have not specified an else-result for the if-loop which results in NULL if none if-part is TRUE and this result will remove the variable which means there is NOTHING on which you could check the len() or isnull() which of course require a parameter.

I assume the simplest solution for your scenario would be just to specify the else-result - maybe just with an empty string of: ''

[Evan0211]

Could you provide an example? I have tried using an else statement in the loop but it still resolves to '-' as a null value.

[marcus_sommer]

Maybe this:

let v1 = if(1>2,true()); let v2 = if(1>2,true(), false());

It are expressions - from the top to the bottom $(v1) and $(v2) directly, then in len() and then within an if-loop.