Display alternate text for NULL

[Bindiya]

Hi,

I have a fact table (f_table) and dimension table (d_table) with below fields.

f_table: country_cd, measure_1

d_table: country_cd, country_desc

In Qlik Sense data manager, I associate both the tables on country_cd field. 

Now I add a bar chart to display measure_1 against country_desc. There are many country codes in my fact table which are not in dimension table. All these are displayed against '-' in my bar chart. But I want to display it as 'Unknown'.

I tried changing the dimension (in bar chart) from just "country_desc" to "if(isnull(country_desc), 'Unknown',country_desc)" but it does not help.

Appreciate any suggestions. Thanks!

[Chanty4u · Accepted solution]

You can use the "ApplyMap" function or "if(isnull(country_desc), 'Unknown', country_desc)" in the script to create a new field with alternate text for NULL values.

[BrunPierre]

Hi,

Just substitute the "country_desc" dimension with Coalesce(country_desc, 'Unknown')

[marcus_sommer]

I suggest to populate the missing records within the dimension-table with a simple concatenation, like:

concatenate(d_table) load distinct country_cd, 'unknown' as country_desc
resident f_table where not exists(country_cd);

[Bindiya]

I tried coalesce too and it did not work either.

[Bindiya]

Thank you for the suggestion. ApplyMap worked.

Regarding the other suggestion, considering my original/before script (from the above screenshot), where would you recommend adding "if(isnull(country_desc), 'Unknown', country_desc)" logic? 

[Bindiya]

Thank you for the suggestion.

Here is my data:

1. This is what I had tried before. Imported 2 tables, associated them on country_cd field, added a table visual and added fields from d_table just to see what values it holds and got below result.

2. Now in load editor, I created a new section after auto generated one, added below script and got the same result as in #1. I.e. description for 999 is still '-'.

Script: "concatenate(d_table) load distinct country_cd, 'unknown' as country_desc
resident f_table where not exists(country_cd);"

Not sure where I went wrong.

[marcus_sommer]

I missed the red-marked part within the above suggestion:

concatenate(d_table) load distinct country_cd, 'unknown' as country_desc
resident f_table where not exists(country_cd, country_cd);

[Bindiya]

Still did not get expected results.

[marcus_sommer]

I wasn't careful enough with the order of the loading (which is always important by exists-approaches and which require sometimes some extra steps) but this one will work:

f_table: load * from f;
d_table: load *, country_cd as temp from d;

concatenate(d_table) load distinct country_cd, 'unknown' as country_desc
resident f_table where not exists(temp, country_cd);
drop fields temp;