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: 
prasad_dumbre
Partner - Creator
Partner - Creator

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
prasad_dumbre
Partner - Creator
Partner - Creator
Author

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]

View solution in original post

3 Replies
sunny_talwar

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

prasad_dumbre
Partner - Creator
Partner - Creator
Author

Yes, those are -ive figs

prasad_dumbre
Partner - Creator
Partner - Creator
Author

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]