Parsing Non-Standard Signs

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.

120-
(200)

Sign indicators like “CREDIT” or “DEBIT” are by default unknown to Qlik and the value will be loaded as text.

300 CREDIT
400 DEBIT

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.

-Rob

Share

3 thoughts on “Parsing Non-Standard Signs”

  1. Nice post Rob!

    When I explain differences among interpretation and format functions in Qlik, people always say something like “oh, yeah, of course, easy and understood”. But, long time after that explanation usualy, they are back with “intractable-QlikView is broken” problems related with this 😀

    By the way, I felt my sweat when I read “Alt#”, I thought I was about to discover a new qlik function!

    Regards,
    Jaime.

    1. Thanks for pointing out my Alt# error. Corrected. The whole format vs interpretation thing does throw people. Witness the number of “how come my dates are repeating in the listbox” questions in the Qlik Community.

Leave a Reply

Your email address will not be published. Required fields are marked *