Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
jblomqvist
Specialist
Specialist

What is the difference between Num and Num# function?

Hi there,

Can anyone explain to me please what is the difference between Num and Num# function?

I tried looking in the Help and it seems to suggest that Num is used for formatting purposes while Num# is for string value conversions?

1 Solution

Accepted Solutions
sasiparupudi1
Master III
Master III

Num() is used to format a given number. it takes the number and the format as inputs

     Num(8.92, '0.0')  gives 8.9

     Num(0.46, '0%')  gives 46%

Below is the QV help

num(expression [ , format-code [ , decimal-sep [ , thousands-sep ] ] ] )

The num function formats the expression numerically according to the string given as format-code. Decimal separator and thousands separator can be set as third and fourth parameters. If the parameters 2-4 are omitted, the number format set in the operating system is used.

Num#() converts a string to a number.You will have to specify to the the function the exact number format of the string and the resultant value is a number with the same format.

     Num#('22%', '0%')  gives 22%

     Num#('2,200', '#,##0') gives 2,200

Below is the QV help

num#(expression [ , format-code[ , decimal-sep [ , thousands-sep] ] ])

The num# function evaluates the expression numerically according to the string given as format-code. Decimal separator and thousands separator can be set as third and fourth parameters. If the parameters 2-4 are omitted, the default number format set by script variables or in the operating system is used.

   

hth

Sasi

View solution in original post

11 Replies
swuehl
MVP
MVP

Num() is for formatting a number  as text (changing the text representation) and Num#() is interpreting a text as number (changing the numeric representation).

Data Types in QlikView

Get the Dates Right

prabhu0505
Specialist
Specialist

num - formatting function to represent within qlikview

num# - interpretation function while reading from source

QV Help definitions:

num(expression [ , format-code [ , decimal-sep [ , thousands-sep ] ] ] )

The num function formats the expression numerically according to the string given as format-code. Decimal separator and thousands separator can be set as third and fourth parameters. If the parameters 2-4 are omitted, the number format settings that have been specified with the Number Interpretation Variables are used in first hand, in second hand the operating system number format settings are used if number interpretation variables have not been set.


num#(expression [ , format-code[ , decimal-sep [ , thousands-sep] ] ])

The num# function evaluates the expression numerically according to the string given as format-code. Decimal separator and thousands separator can be set as third and fourth parameters. If the parameters 2-4 are omitted, the default number format set by script variables or in the operating system is used.

jblomqvist
Specialist
Specialist
Author

Hi Swuehl,

Does this mean if a number is formatted as 3.44 but is stored as a string, the num# function converts it to the exact format in numeric values? So the result is 3.44 stored as a number.

sasiparupudi1
Master III
Master III

Num() is used to format a given number. it takes the number and the format as inputs

     Num(8.92, '0.0')  gives 8.9

     Num(0.46, '0%')  gives 46%

Below is the QV help

num(expression [ , format-code [ , decimal-sep [ , thousands-sep ] ] ] )

The num function formats the expression numerically according to the string given as format-code. Decimal separator and thousands separator can be set as third and fourth parameters. If the parameters 2-4 are omitted, the number format set in the operating system is used.

Num#() converts a string to a number.You will have to specify to the the function the exact number format of the string and the resultant value is a number with the same format.

     Num#('22%', '0%')  gives 22%

     Num#('2,200', '#,##0') gives 2,200

Below is the QV help

num#(expression [ , format-code[ , decimal-sep [ , thousands-sep] ] ])

The num# function evaluates the expression numerically according to the string given as format-code. Decimal separator and thousands separator can be set as third and fourth parameters. If the parameters 2-4 are omitted, the default number format set by script variables or in the operating system is used.

   

hth

Sasi

jblomqvist
Specialist
Specialist
Author

Hi Sasidhar,

What if a user uses the num# without any formatting and just the field?

For example num#(valuefield) where value field contains 5000 but the field is actually a string.

Will num# function just convert that to 500 but in a numeric format?

sasiparupudi1
Master III
Master III

yes

Peter_Cammaert
Partner - Champion III
Partner - Champion III

Close...

QlikView does a lot of behind-the-scenes conversions, and it does this all the time. If your system defaults, or QlikView defaults (see the SET statements at the beginning of every script) are correctly set, and your input data is formatted in a consistent way acording to these defaults, then you won't ever need num() or num#(). Except if you want to deviate from the defaults or whenever you have to deal with mixed formatting. Imagine that most of your data is formatted according to US-rules, and some of the data reverses dots and comma's. In that case you will have to intervene to get the data interpretation right. Num() and Num#() will prove to be very useful.

When Stefan talks about text representation and numerical representation, he's actually referring to the two faces of every numerical value in QlikView. Internally QlikView stores numerical values as dual values, meaning that they have both a numerical and a text representation. Calculations are performed using numerical values (obviously for performance reasons), output is done using the text representation. The numerical representation may be missing, in which case you can't do any calculation with these values (and a listbox will align them on the left side). The num#() function will enable you to convert a 3.44 text string into a numerical (binary) value in countries where decimals are separated with commas, while QlikView may not be able to do so automatically because of the wrong default format strings.

Best

Peter

jagan
Luminary Alumni
Luminary Alumni

Hi,

Num#() - is used to convert a string to numeric format, where as Num() is used to format the number.

Simply Num#() is used for conversion

Num() - is used for display purpose.

Example: In you column assume that we have ^ as decimal separator and we need . as decimal separator then we can use both the functions like this

=Num(Num#('2345^00', '##0^00'),  '##0.00')

Num#('2345^00', '##0^00') - Converts text format to numeric format

Num(Num#('2345^00', '##0^00'),  '##0.00') - will format the display of the number

Hope this helps you.

Regards,

Jagan.

qlikviewwizard
Master II
Master II

Hi John,

Did you able find the differences? Thank you.