Why do you need them as 0 though ? From a database & memory perspective a NULL takes no space and memory and therefore makes things faster. A 0 takes up space and memory (you're basically densifying the data) and will make Tableau slower (even just a tiny bit) because there is a value that needs to be referenced.
Are you having problems in Desktop using null values ? I'd be aiming to solve that problem in Desktop, not in Prep
A few thoughts:
- Chris' question about whether NULLs need to be replaced is a good one to consider - though there are definitely times when 0 is analytically correct / useful or practical versus NULL.
- 120 fields is a lot to manually adjust - but that still might be the most efficient way. Note that you should be able to simply select the NULL value in each column and press the number 0 on the keyboard to replace it without having to select any group and replace options (the typing will automatically do the group and replace)
- Another possibility is to Pivot the fields from Columns to Rows so that you have all 120 fields now contained in a single field. Then do a single replace of the NULL in that field. Then do another Pivot from Rows to Columns to get back to the original structure with all NULLs replaced with 0s.
Hope that helps!
Thanks both. I was unaware that this would densify the data. These are costs fields and null wasnt making sense, but as you pointed out I will think about dealing with this in Desktop like you suggested, but this would still mean writing 100 ZN functions for the fields because I will be using most in my calculations.
Do you really need a ZN in Desktop though ? Depending on what you are doing, maybe you don't
Thanks! will try the thrid option and see.