What is the difference between Num and Num# function?

[jblomqvist]

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?

[swuehl]

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]

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]

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 · Accepted solution]

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]

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]

yes

[Peter_Cammaert]

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]

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]

Hi John,

Did you able find the differences? Thank you.