Num - script and chart function

Num() formats an expression numerically in the number format set in the system variables in the data load script, or in the operating system, unless a format string is supplied, and optional decimal and thousands separators.

Syntax:

Num(number[, format[, dec_sep [, thou_sep]]])

Return data type: dual

The Num function returns a dual value with both the string and the number value. The function takes the numeric value of the input expression and generates a string representing the number.

Arguments:

Argument	Description
number	The number to be formatted.
format	String describing how the resulting string is to be formatted. If omitted, the number format set in the operating system is used.
dec_sep	String specifying the decimal number separator. If omitted, the MoneyDecimalSep value set in the data load script is used.
thou_sep	String specifying the thousands number separator. If omitted, the MoneyThousandSep value set in the data load script is used.

The examples below assume the following default settings:

Number format setting 1: # ##0
Number format setting 2: #,##0

For more information on number formatting see Conventions for number and time formats.

Chart expression	Results	Setting 1	Setting 2
Num( 35648.375 , '0.0' )	String	35 648 375	35648.375
Num( 35648.375 , '0.0' )	Number	35648375	35648.375
Num( 35648, '#,##0.##', '.' , ',' )	String	35,648.00	35,648.00
Num( 35648, '#,##0.##', '.' , ',' )	Number	35648	35648
Num( pi( ), '0,00' )	String	3,14	003
Num( pi( ), '0,00' )	Number	3.141592653	3.141592653

Load script

Num can be used in load script to format a number, even if the thousand and decimal separators are already set in the script. The load script below includes specific thousand and decimal separators but then uses Num to format data in different ways.

In the Data load editor, create a new section, and then add the example script and run it. Then add, at least, the fields listed in the results column to a sheet in your app to see the result.

SET ThousandSep=','; SET DecimalSep='.'; Transactions: Load *, Num(transaction_amount) as [No formatting], Num(transaction_amount,'0') as [0], Num(transaction_amount,'#,##0') as [#,##0], Num(transaction_amount,'# ###,00') as [# ###,00], Num(transaction_amount,'# ###,00',',',' ') as [# ###,00 , ',' , ' '], Num(transaction_amount,'#,###.00','.',',') as [#,###.00 , '.' , ','], Num(transaction_amount,'$#,###.00') as [$#,###.00], ; Load * Inline [ transaction_id, transaction_date, transaction_amount, transaction_quantity, discount, customer_id, size, color_code 3750, 20180830, 12423.56, 23, 0,2038593, L, Red 3751, 20180907, 5356.31, 6, 0.1, 203521, m, orange 3752, 20180916, 15.75, 1, 0.22, 5646471, S, blue 3753, 20180922, 1251, 7, 0, 3036491, l, Black 3754, 20180922, 21484.21, 1356, 75, 049681, xs, Red 3756, 20180922, -59.18, 2, 0.333333333333333, 2038593, M, Blue 3757, 20180923, 3177.4, 21, .14, 203521, XL, Black ];

Results

Qlik Sense table showing the results from different uses of the Num function in the load script. The fourth column of the table contains incorrect formatting use, for example purposes.
No formatting	0	#,##0	# ###,00	# ###,00 , ',' , ' '	#,###.00 , '.' , ','	$#,###.00
-59.18	-59	-59	-59###,00	-59,18	-59.18	$-59,18
15.75	16	16	16###,00	15,75	15.75	$15,75
1251	1251	1,251	1251###,00	1 251,00	1,251.00	$1,251.00
3177.4	3177	3,177	3177###,00	3 177,40	3,177.40	$3,177.40
5356.31	5356	5,356	5356###,00	5 356,31	5,356.31	$5,356.31
12423.56	12424	12,424	12424###,00	12 423,56	12,423.56	$12,423.56
21484.21	21484	21,484	21484###,00	21 484,21	21,484.21	$21,484.21

Load script

Num can be used in a load script to format a number as a percentage.

In the Data load editor, create a new section, and then add the example script and run it. Then add, at least, the fields listed in the results column to a sheet in your app to see the result.

SET ThousandSep=','; SET DecimalSep='.'; Transactions: Load *, Num(discount,'#,##0%') as [Discount #,##0%] ; Load * Inline [ transaction_id, transaction_date, transaction_amount, transaction_quantity, discount, customer_id, size, color_code 3750, 20180830, 12423.56, 23, 0,2038593, L, Red 3751, 20180907, 5356.31, 6, 0.1, 203521, m, orange 3752, 20180916, 15.75, 1, 0.22, 5646471, S, blue 3753, 20180922, 1251, 7, 0, 3036491, l, Black 3754, 20180922, 21484.21, 1356, 75, 049681, xs, Red 3756, 20180922, -59.18, 2, 0.333333333333333, 2038593, M, Blue 3757, 20180923, 3177.4, 21, .14, 203521, XL, Black ];

Results

Qlik Sense table showing the results of the Num function being used in the load script to format percentages.
discount	Discount #,##0%
0.333333333333333	33%
0.22	22%
0	0%
.14	14%
0.1	10%
0	0%
75	7,500%

Num - script and chart function

Feedback