Summary: I demonstrate using Num#() and Alt() functions to read numbers with non-standard signs. Download link at bottom of post.
When reading from text files, the default Qlik interpretation of numeric sign syntax is as follows:
100: No prefix, positive number
+65: “+” prefix, positive number
-110: “-” prefix, negative number
In the default interpretation a “-” suffix or “()” are not recognized as valid numbers and are loaded as text values.
Sign indicators like “CREDIT” or “DEBIT” are by default unknown to Qlik and the value will be loaded as text.
In a Table Box, Chart Dimension or Listbox, numeric values are by default right aligned and text values are left aligned by default. This is a simple way to check what is text and what is numeric.
Aggregation functions, such as Sum(), treat text values as zero. So a chart using the example numbers above would look like this:
I can utilize the num#() script function to tell Qlik how to read numbers using other than default signs. For example, to indicate that a trailing minus is used:
Num#(Sample, '0;0-') as Amount2
That takes care of “120-“. But what about the other odd signs? I can nest multiple num#() functions inside Alt() to test various patterns:
Alt( Num#(Sample, '0;0-') ,Num#(Sample, '0;(0)') ,Num#(Sample, '0 CREDIT;0 DEBIT') ) as Amount3
The chart demonstrates that all values are correctly recognized as numbers. They do retain their input values as the display format.
If I want to harmonize the display formats, I can add an outer Num() function to indicate the display format for all.
Num( Alt( Num#(Sample, '0;0-') ,Num#(Sample, '0;(0)') ,Num#(Sample, '0 CREDIT;0 DEBIT') ) ,'#,##0.00;(#,##0.00)') as Amount4
Downloadable QV & QS examples to accompany this post can be found here.