CODEWITHSUNDEEP
nulltableau-api
Saaru Lindestøkke
Saaru Lindestøkke

Reputation: 2544

How to set a value to NULL across the whole dataset?

I have a dataset where unknown, missing or otherwise NULL-like values are coded as -9999999. This is done in Dimensions and Measurement columns.

How can I instruct Tableau to treat -9999999 as NULL in the whole dataset?

I have .csv, Excel and .shp files, but I'm trying to find out a general way to do this in Tableau.

Things I have tried

Creating a calculated column

I have created a calculated column with:

IFF([Column] == -9999999, NULL, [Column])

However, this is too tedious to do with all columns.

Creating an alias

I have attempted to create aliases, however, this only works for Dimensions not Measures.

Exporting as .csv and editing

I have exported my dataset as a .csv file, then edited in a text-editor (replaced all occurrences of -9999999 with NULL) and re-loaded the data from the .csv file. This works, but feels sub-optimal. For example, .shp files cannot be exported lossless to a .csv file.

Upvotes: 1

Views: 129

Answers (1)

Sam M
Sam M

Reputation: 4166

Tableau does not natively have functionality to treat all matching values in a dataset and convert them to NULL. You have a few options.

  1. Calculated fields. You tried this and I agree, it can be tedious with many dimensions and measures. Same with aliases, which would be even more cumbersome.
  2. Change the output from the source system that generates your data. If it's a database, create a view that does the conversion for you.
  3. If you are using a data extract, use the Tableau Extract API and write a wrapper program that will convert the values to null. This could easily be done with loops and hit all the fields at once.
  4. If you have an ETL tool such as Informatica or Talend, run the source data through it first to do the data conversion.
  5. If it's a file-based datasource, use a program that has automated search-and-replace capabilities. A command-line GREP program or Notepad++ might do the trick.

Upvotes: 1

Related Questions