How to replace null value after joing two fields in calculated dimension in chart

[Anna3]

Hi,

I have to join two fields in calculated dimension:

FieldA & ', ' & FieldB

If there is null value I want to print some text: 'no value'.

One of my solution which doesn't work:

if(isnull(FieldA & ', ' & FieldB),'no value',FieldA & ', ' & FieldB)

I tried different combinations but nothing happend. I'm still getting '-'. Is it possible to change null for text in that case?

* I don't want to join fields in script as I want users to see in selection box two separate fields

[a_mullick]

Hi,

You have to do some extra work to have expressions as a dimension, and as a rule the AGGR() function helps. Here, I've aggregated over a primary key field,  _Key,  but you can substitute whatever you have that functions as a primary key in the table that contains FieldA and FieldB.

If(IsNull(aggr(Only(FieldA), _Key)) and IsNull(aggr(Only(FieldB), _Key)) , 'Not Known', FieldA & ' ,' & FieldB)

Hope that helps,

Azam

[marcus_sommer]

Personally I would tend to do this within the script - with both fields and probably also within an additionally concat-field. Nevertheless something like the following should work within the UI:

if(len(trim(FieldA)), 'no value', FieldA) & ' + ' & if(len(trim(FieldB)), 'no value', FieldB)

- Marcus

[Anna3 · Accepted solution]

Thank You for answering.

I had to change it a little but it quit works.

if(IsNull(aggr(Only(FieldA), _key)) and IsNull(aggr(Only(FieldB), _key)) ,'no value', aggr(only(FieldA&', ' & FieldB), _key))

It looked good in chart but after clicking on dimension it made selection on _key field instead of FieldA and FieldB.

However I needed trigger anyway to select FieldA when selecting _key, so it works almost perfectly now. Thank You 🙂