Replacing null values and formatting field as numbers
In my data model, I aggregated accounts and assets by EmployeeId number. Many employees do not have assets/accounts, but because I want to retain every employee in the data model, I left joined the summary statistics to the EmployeeId list. To clean the data, I want to replace the null values with 0 and then apply formatting.
The code I have below doesn't work correctly. The null values in the accounts field are replaced with zero, but they are formatted as text instead of numbers. In the assets field, the original values are formatted as numbers, not currency. And the previously null values are "$0.00" which I would expect. I played around with the num# function and changing the nesting order but couldn't get this to work. Any idea what might be going on or how I can fix it? Thank you for the help.
FinalCleanTable: NoConcatenate Load EmployeeId, num(if(isnull(Accounts), 0, Accounts), '#,##0.') as Accounts, num(if(isnull(Assets), 0, Assets), '$#,##0.00') as Assets Resident FinalTable;
You can get unexpected results when adding new formatted values to an existing field. Rather than having you post more code, I'll just say that I usually replace the joined nulls using this technique.
Slightly an aside, but Alt(Accounts,0) is a cleaner way to achieve the same thing. This has the added benefit of "confirming" that Accounts is a number, because Alt won't pick out a field that lacks a numeric representation.
