topic convert neg number in QlikView

convert neg number

qlikhans — Mon, 14 Oct 2013 12:06:13 GMT

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

Re: convert neg number

rustyfishbones — Mon, 14 Oct 2013 12:12:55 GMT

can you attach the a sampl txt file please

Re: convert neg number

MayilVahanan — Mon, 14 Oct 2013 12:13:21 GMT

May be try like this

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

Re: convert neg number

dvasseur — Mon, 14 Oct 2013 12:25:11 GMT

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);

Re: convert neg number

CELAMBARASAN — Mon, 14 Oct 2013 12:30:36 GMT

Did you tried this?

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

Re: convert neg number

qlikhans — Mon, 11 Nov 2013 09:13:33 GMT

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