topic Re: What is the difference between Num and Num# function? in QlikView

What is the difference between Num and Num# function?

jblomqvist — Mon, 17 Aug 2015 19:34:59 GMT

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?

Re: What is the difference between Num and Num# function?

swuehl — Mon, 17 Aug 2015 19:37:14 GMT

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

Re: What is the difference between Num and Num# function?

prabhu0505 — Mon, 17 Aug 2015 19:38:43 GMT

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.

Re: What is the difference between Num and Num# function?

jblomqvist — Tue, 18 Aug 2015 08:43:40 GMT

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.

Re: What is the difference between Num and Num# function?

sasiparupudi1 — Tue, 18 Aug 2015 08:54:23 GMT

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 ] ] ] )

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] ] ])

hth

Sasi

Re: What is the difference between Num and Num# function?

jblomqvist — Tue, 18 Aug 2015 09:00:01 GMT

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?

Re: What is the difference between Num and Num# function?

sasiparupudi1 — Tue, 18 Aug 2015 09:04:25 GMT

yes

Re: What is the difference between Num and Num# function?

Peter_Cammaert — Tue, 18 Aug 2015 09:11:11 GMT

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

Re: What is the difference between Num and Num# function?

jagan — Tue, 18 Aug 2015 09:51:59 GMT

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.

Re: What is the difference between Num and Num# function?

qlikviewwizard — Tue, 18 Aug 2015 14:22:00 GMT

Hi John,

Did you able find the differences? Thank you.

Re: What is the difference between Num and Num# function?

Anonymous — Wed, 05 Oct 2016 09:29:42 GMT

Perfect Explanation

Re: What is the difference between Num and Num# function?

Qliksense_User — Tue, 25 Feb 2020 16:44:21 GMT

Num#('22%', '0%') does not give 22%, instead it gives 0.22

Num#('2,200', '#,##0') does not give 2,200. instead it gives 2.2K

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