convert neg number

[qlikhans]

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

[rustyfishbones]

can you attach the a sampl txt file please

[MayilVahanan]

Hi

May be try like this

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

[dvasseur]

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 · Accepted solution]

Did you tried this?

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

[qlikhans]

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