topic Re: How to replace null value after joing two fields in calculated dimension in chart in QlikView

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

Anna3 — Tue, 18 Feb 2020 08:06:03 GMT

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

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

a_mullick — Tue, 18 Feb 2020 09:05:50 GMT

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

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

marcus_sommer — Tue, 18 Feb 2020 12:37:54 GMT

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

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

Anna3 — Tue, 18 Feb 2020 13:16:43 GMT

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 🙂