topic Re: Inconsistent expression results for isnull in QlikView

Inconsistent expression results for isnull

sambrown — Fri, 23 Feb 2018 15:18:11 GMT

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)

Col	AccountName	AltAccountName	Expression (as dimension)
1	Dickinson	Dickinson	Dickinson
2	-	Pannell	Pannell
3	-	Owen	-
4	Buzzacott	-	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

Re: Inconsistent expression results for isnull

sunny_talwar — Fri, 23 Feb 2018 17:35:42 GMT

Try with Len(Trim()) instead of IsNull

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

Re: Inconsistent expression results for isnull

rwunderlich — Fri, 23 Feb 2018 17:39:05 GMT

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

Re: Inconsistent expression results for isnull

sunny_talwar — Fri, 23 Feb 2018 17:40:57 GMT

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

rwunderlich — Fri, 23 Feb 2018 17:49:58 GMT

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

Re: Inconsistent expression results for isnull

Digvijay_Singh — Fri, 23 Feb 2018 18:10:56 GMT

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.

Set NullInterpret ='-';

LOAD * inline [

Col,AccountName,AltAccountName

1,Dickinson,Dickinson

2,-,Pannell

3,-,Owen

4,Buzzacott,-

];

Re: Inconsistent expression results for isnull

sunny_talwar — Fri, 23 Feb 2018 18:15:26 GMT

Sounds like a plan...

Re: Inconsistent expression results for isnull

pablolabbe — Wed, 28 Feb 2018 09:16:58 GMT

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

Re: Inconsistent expression results for isnull

sambrown — Wed, 28 Feb 2018 10:24:45 GMT

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

rwunderlich — Wed, 28 Feb 2018 18:20:41 GMT

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

-Rob

Re: Inconsistent expression results for isnull

sambrown — Thu, 01 Mar 2018 15:21:12 GMT

Oh yes of course. I had it under a different name in my app so I confused myself.

I can confirm that did work, thank you so much.

Re: Inconsistent expression results for isnull

sunny_talwar — Thu, 01 Mar 2018 16:31:08 GMT

Rob - It appears that you are right (because your response was marked as Correct)... would you be able to explain why this wouldn't work without Aggr()? or why do we need Aggr()?

Re: Inconsistent expression results for isnull

sunny_talwar — Thu, 01 Mar 2018 17:13:33 GMT

But this did not work?

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

Re: Inconsistent expression results for isnull

rwunderlich — Thu, 01 Mar 2018 23:47:06 GMT

Samuel,

Can you please post a screenshot of your data model?

Thanks,

Rob

Re: Inconsistent expression results for isnull

Lucke_Hallbergson — Fri, 02 Mar 2018 08:51:02 GMT

2 other ways are;

SET NULLINTERPRET=''; //interpret blanks as null

[Col]:

LOAD * INLINE

[

Col,AccountName,AltAccountName

1,Dickinson,Dickinson

2,,Pannell

3,,Owen

4,Buzzacott,

](delimiter is ',');

[Col]:

LOAD

[Col],

If (Match([AccountName], ''), NULL(), [AccountName]) AS [AccountName],

If (Match([AltAccountName], ''), NULL(), [AltAccountName]) AS [AltAccountName];

LOAD * INLINE

[

Col,AccountName,AltAccountName

1,Dickinson,Dickinson

2,,Pannell

3,,Owen

4,Buzzacott,

](delimiter is ',');

Re: Inconsistent expression results for isnull

sambrown — Fri, 02 Mar 2018 10:16:08 GMT

Hi,

Is this what you were looking for?

Re: Inconsistent expression results for isnull

rwunderlich — Sat, 03 Mar 2018 19:26:55 GMT

Perfect Samuel, thanks.

Sunny, I think you and others posting suggestions in this thread were assuming that all fields were in the same table. Based on the problem Samuel describes, I was guessing that the fields were in separate tables. Why is aggr() required in this case? Assume that the fact id field is "QuoteId" from the table model ("Col" in the first picture posted).

When coded as an Expression:

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

will work because there would only be one AltAccountName / AccountName associated with a QuoteId Dimension. i.e. the chart dimensions are used.

When coded as Calculated Dimension, it will not work. The other Dimensions in the chart are not applied when evaluating a calculated dimension. The evaluation will be done over each value of AltAccountName, which could yield different AltAccountName / AccountName pairs for the same AltAccountName.

Adding the aggr() runs the logic over each QuoteId and therefore means that we are dealing with the combination of QuoteId / AltAccountName / AccountName.

I could probably have explained it better, but there it is.

-Rob

http://masterssummit.com

http://qlikviewcookbook.com