topic Re: Replacing null values and formatting field as numbers in App Development

Replacing null values and formatting field as numbers

maxcsquared — Mon, 23 Dec 2024 20:38:37 GMT

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;

Drop Table FinalTable;

Re: Replacing null values and formatting field as numbers

rwunderlich — Mon, 23 Dec 2024 23:53:26 GMT

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.

https://qlikviewcookbook.com/2013/01/filling-default-values-using-mapping/

And I handle the formatting of the values like this:

https://qlikviewcookbook.com/2016/07/touchless-formatting/

-Rob
http://www.easyqlik.com
http://masterssummit.com
http://qlikviewcookbook.com

Re: Replacing null values and formatting field as numbers

Or — Tue, 24 Dec 2024 07:36:42 GMT

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.

Re: Replacing null values and formatting field as numbers

maxcsquared — Thu, 26 Dec 2024 15:26:49 GMT

Thank you for the resources, really appreciate it!

Re: Replacing null values and formatting field as numbers

maxcsquared — Thu, 26 Dec 2024 16:07:23 GMT

And a few things to clarify:

what does the Autogenerate function do in the formatting and mapping scripts you linked to?
Do the MAP USING statements get applied after reloading the existing table or before?

Thank you.

Re: Replacing null values and formatting field as numbers

rwunderlich — Thu, 26 Dec 2024 23:41:29 GMT

"Autogenerate 1" means generate one row in this mapping table -- i.e. do this Load statement once.

https://help.qlik.com/en-US/cloud-services/Subsystems/Hub/Content/Sense_Hub/Scripting/ScriptRegularStatements/Load.htm

"Do the MAP USING statements get applied after reloading the existing table or before?"

After.

-Rob