4 Replies Latest reply on Jan 30, 2014 5:38 AM by Matt Lutton

# Removing Nulls from Combined Dimensions

I have a data set in a data warehouse that has 35 columns. 12 columns are a 'Difference' field (Difference_1, Difference_2 etc) that contains values ranging from Null, 0 to 120. I need to combine these 12 fields and remove the Nulls. So i can drop 1 combined field onto columns shelf and shows these values along the X axis, without the Nulls. These 12 columns do contain duplicates, so in the 1st difference column it may contain 100 Nulls, and many variations of 1,2,3,.... etc, The same will be for the other 11 columns. How do I do this?

I did try creating a combined field (with these 12 fields) and drop into filters, it doesnt work as in the filter it will show null,null,null,null,1,null,null,null,null,null,null,null This will be displayed for every number. So effectively there were 120 of these.

• ###### 1. Re: Removing Nulls from Combined Dimensions

Hi,

You need to convert the null values to zero, if that suits you. Like,

```IIF(ISNULL([Difference_1)),0,[Difference_1))
```

This converts the null values and eases up the calculations. You may also refer to Handling Null Values | Tableau Software

Cheers !!

• ###### 2. Re: Removing Nulls from Combined Dimensions

thanks. The problem is I cant use 0, and that is a valid field that I need. I need to somehow remove them completely.

• ###### 3. Re: Removing Nulls from Combined Dimensions

If you want to remove nullable values from a set of columns in SQL you can use the COALESCE function, it's a multiple parameter ISNULL, so you can do COALESCE(Diff1,Diff2,Diff3,Diff4,...,DiffN) and it will return the first non-null. If you can't change the view you can pass it through using the RAWSQL_* functions (the provider would need to support COALESCE though). e.g. RAWSQL_STR("COALESCE(%1,%2...%n)",[Diff1],[Diff2],...[DiffN]) This assumes that you will only have one non-null value in a given row/set of columns, which I think fits your example.

• ###### 4. Re: Removing Nulls from Combined Dimensions

You may be able to filter the nulls at the data source level using the "Edit Data Source Filters" option.