topic Re: Issue while converting sting to number using num# in QlikView

Issue while converting string to number using num#

prasad_dumbre — Fri, 20 Apr 2018 12:49:32 GMT

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.

Re: Issue while converting sting to number using num#

sunny_talwar — Fri, 20 Apr 2018 12:54:34 GMT

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

Re: Issue while converting sting to number using num#

prasad_dumbre — Fri, 20 Apr 2018 13:06:12 GMT

Yes, those are -ive figs

Re: Issue while converting string to number using num#

prasad_dumbre — Fri, 20 Apr 2018 13:29:18 GMT

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]