Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
sambrown
Contributor II
Contributor II

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
rwunderlich
Partner Ambassador/MVP
Partner Ambassador/MVP

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

View solution in original post

16 Replies
sunny_talwar

Try with Len(Trim()) instead of IsNull

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

rwunderlich
Partner Ambassador/MVP
Partner Ambassador/MVP

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

sunny_talwar

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()?

rwunderlich
Partner Ambassador/MVP
Partner Ambassador/MVP

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

Digvijay_Singh

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,-

];

sunny_talwar

Sounds like a plan...

pablolabbe
Luminary Alumni
Luminary Alumni

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
Contributor II
Contributor II
Author

Hi,

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

rwunderlich
Partner Ambassador/MVP
Partner Ambassador/MVP

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

-Rob