Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Highlighted
Partner
Partner

Issue while converting string to number using num#

Dear Colleagues, We are pulling data from SAP spools & facing issue while converting a field into number. It contains data like: 0.98-
0.99
0.99-
1,000,152.23
1,000,152.23-
1,000,395.46
1,000,395.46-
1,000.64
1,000.81
1,000.81- We tried couple of num# combinations, but they are unable to identify it correctly as a number. Kindly suggest how to interpret this data correctly.

1 Solution

Accepted Solutions
Partner
Partner

Re: Issue while converting string to number using num#

Found the issue was with our system variable.
With below updated system variable, num# worked perfectly:
SET ThousandSep=',';
SET DecimalSep='.';// Earlier here ',' was there
SET MoneyThousandSep=',';
SET MoneyDecimalSep='.';
SET MoneyFormat='#,##0.00;(#,##0.00)';
SET TimeFormat='hh:mm:ss';
SET DateFormat='YYYYMMDD';
SET TimestampFormat='YYYYMMDD hh:mm:ss[.fff]';
SET MonthNames='Jan;Feb;Mar;Apr;May;Jun;Jul;Aug;Sep;Oct;Nov;Dec';
SET DayNames='Mon;Tue;Wed;Thu;Fri;Sat;Sun';
SET HidePrefix = '_';


num(alt(num#([Amount in local cur.]),-num#([Amount in local cur.],'#-'))) as [FOREX Amount]

3 Replies

Re: Issue while converting sting to number using num#

What does the - at the end means? Does it mean negative 0.98 (-0.98)?

Partner
Partner

Re: Issue while converting sting to number using num#

Yes, those are -ive figs

Partner
Partner

Re: Issue while converting string to number using num#

Found the issue was with our system variable.
With below updated system variable, num# worked perfectly:
SET ThousandSep=',';
SET DecimalSep='.';// Earlier here ',' was there
SET MoneyThousandSep=',';
SET MoneyDecimalSep='.';
SET MoneyFormat='#,##0.00;(#,##0.00)';
SET TimeFormat='hh:mm:ss';
SET DateFormat='YYYYMMDD';
SET TimestampFormat='YYYYMMDD hh:mm:ss[.fff]';
SET MonthNames='Jan;Feb;Mar;Apr;May;Jun;Jul;Aug;Sep;Oct;Nov;Dec';
SET DayNames='Mon;Tue;Wed;Thu;Fri;Sat;Sun';
SET HidePrefix = '_';


num(alt(num#([Amount in local cur.]),-num#([Amount in local cur.],'#-'))) as [FOREX Amount]