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: 
qlikhans
Creator
Creator

convert neg number

Hi all,

I want to upload a txt file, in which negative numbers are formatted as 1,111.11-.

QV does not recognize this as a number at all.

My attempt was in script to use (AmountDC,'#,##0.0;#,##0.0-') in the script. That one only results in error message and a total disappearing of the numbers.

Who knows what to do?

krgrds

Hans

1 Solution

Accepted Solutions
CELAMBARASAN
Partner - Champion
Partner - Champion

Did you tried this?

=Num(Num#(AmountDC, '#,##0.0;#,##0.0-'))

View solution in original post

5 Replies
rustyfishbones
Master II
Master II

can you attach the a sampl txt file please

MayilVahanan

Hi

May be try like this

=If(FindOneOf(AmountDC,'-') >1,SubField(AmountDC,'-')*-1,AmountDC)

Thanks & Regards, Mayil Vahanan R
Please close the thread by marking correct answer & give likes if you like the post.
dvasseur
Partner - Creator III
Partner - Creator III

Try this:

LOAD

  If(Right(AmountDC, 1) = '-',

    -Left(AmountDC, Index(AmountDC, '-', -1)-1),

    AmountDC

  ) AS AmountDC

FROM

data.txt (txt, codepage is 1252, embedded labels, delimiter is '\t', msq);

CELAMBARASAN
Partner - Champion
Partner - Champion

Did you tried this?

=Num(Num#(AmountDC, '#,##0.0;#,##0.0-'))

qlikhans
Creator
Creator
Author

Hi all,

Sorry for the late reply. Thank you all for the suggestions. It is working fine now with the suggestion of Celambarasan.

kgrds

Hans