Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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
Try with Len(Trim()) instead of IsNull
If(Len(Trim(AltAccountName)) = 0, AccountName, AltAccountName)
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
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()?
Let's see if it fixes his problem and then I'll venture an explanation
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,-
];
Sounds like a plan...
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
Hi,
Thanks for looking into this. I'm a bit confused as to what I'm supposed to be doing with 'col' in this?
"Col" is the dimension name, I took it from the first screenshot you posted.
-Rob