Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Highlighted
sambrown
New Contributor

Inconsistent expression results for isnull

Hi,

I'm fairly new to qlik, so I apologise if this is a simple problem. What I'm trying to do is an isnull expression to populate a field based on two fields from different tables. My expression is:

=if(isnull(AltAccountName), AccountName, AltAccountName)

ColAccountNameAltAccountName

Expression

(as dimension)

1

DickinsonDickinsonDickinson
2-PannellPannell
3-Owen-
4Buzzacott-Buzzacott

For rows 1, 2 and 4 it's working just as I want. But I can't figure out why row 3 is producing a different result to row 2 when on the surface it looks exactly the same. Can anyone suggest as to what could be causing this or is there a workaround I could try?

I don't know if this is a clue, but if I write the exact same expression down as a measure rather than dimension, it works perfectly . But unfortunately I'm looking to filter these results in the app.

Doing an =len on both field returns either the length or a null value. So using len=0 does thes same as the above.

Many thanks in advance

Sam

1 Solution

Accepted Solutions

Re: Inconsistent expression results for isnull

For this to work correctly as a Dimension, you will need to wrap it in an aggr:

aggr(if(isnull(AltAccountName), AccountName, AltAccountName) , Col)


-Rob

http://masterssummit.com

http://qlikviewcookbook.com

16 Replies
MVP
MVP

Re: Inconsistent expression results for isnull

Try with Len(Trim()) instead of IsNull

If(Len(Trim(AltAccountName)) = 0, AccountName, AltAccountName)

Re: Inconsistent expression results for isnull

For this to work correctly as a Dimension, you will need to wrap it in an aggr:

aggr(if(isnull(AltAccountName), AccountName, AltAccountName) , Col)


-Rob

http://masterssummit.com

http://qlikviewcookbook.com

MVP
MVP

Re: Inconsistent expression results for isnull

Rob -

I don't want to sound as if I am doubting you, but I thought it would work without Aggr()? Do you know why it wouldn't work without Aggr()?

Re: Inconsistent expression results for isnull

Let's see if it fixes his problem and then I'll venture an explanation

Digvijay_Singh
Honored Contributor III

Re: Inconsistent expression results for isnull

All expressions work, I think the problem is, data doesn't have null, its some other character.

See the sample, when I created null in place of '-', all expressions work.

Also the information shared doesn't appear correct in my sample that the expression works for 2nd row, why not for 3rd. It work as desired for 3rd as well in my sample. Using Qlikview 12.

Capture.PNG

Set NullInterpret ='-';


LOAD * inline [

Col,AccountName,AltAccountName

1,Dickinson,Dickinson

2,-,Pannell

3,-,Owen

4,Buzzacott,-

];

MVP
MVP

Re: Inconsistent expression results for isnull

Sounds like a plan...

pablolabbe
Valued Contributor II

Re: Inconsistent expression results for isnull

When applicable please mark the appropriate replies as CORRECT. This will help community members and Qlik Employees know which discussions have already been addressed and have a possible known solution. Please mark threads as HELPFUL if the provided solution is helpful to the problem, but does not necessarily solve the indicated problem. You can mark multiple threads as HELPFUL if you feel additional info is useful to others

sambrown
New Contributor

Re: Inconsistent expression results for isnull

Hi,

Thanks for looking into this. I'm a bit confused as to what I'm supposed to be doing with 'col' in this?

Re: Inconsistent expression results for isnull

"Col" is the dimension name, I took it from the first screenshot you posted.

-Rob

Community Browser