Inconsistent expression results for isnull

[sambrown]

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

[sambrown]

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.

[sunny_talwar]

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

[sunny_talwar]

But this did not work?

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

[rwunderlich]

Samuel,

Can you please post a screenshot of your data model?

Thanks,

Rob

[Lucke_Hallbergson]

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 ',');

Or

[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 ',');

[sambrown]

Hi,

Is this what you were looking for?

[rwunderlich]

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