no identification for round brackets in csv

[maxsheva]

Hi All,

I have csv with a column where positive and negative values.

For negative format in csv uses round brackets e.g. (2.30)

QlikView doesn't identify it as minus. What is the best way to convert into negative like -2.30?

I think about replace or purge but probably QlikView has other standard solution for such issue?

Thanks

[kfoudhaily]

hello,

it would be useful if you provide sample data ;

supposing you field is named A, try this;

load

IF(left(A,1)='(',num(replace(Replace(A, '(' , ''),')' , ''))*-1,A)  as A

from ...

regards,

[maxsheva]

Hi Khalil,

Thanks for suggestion but I am looking for some basic/standard solution from QlikView.

Currently I am using simple expression If(Field<0,-purgechar(Field,'()'),Field)

[vishsaggi]

Did your expression giving any error? Why don't you just use like

= Purgechar(Field, '()') * -1

[maxsheva]

Thanks @vishsaggi

My expression is working well.

If there no standard solution I will keep mine in script.

Thanks All.

[MarcoWedel · Accepted solution]

Hi,

one solution might be:

table1:
LOAD *,
    Num#(String,'0.##;(0.##)') as Number,
    Num(Num#(String,'0.##;(0.##)')) as Value
Inline [
    String
    2.20
    (2.30)
    2.4
    (2.5)
    3
    (4)
];

hope this helps

regards

Marco

[maxsheva]

Much appreciated Marco!

It is that what I expected to find.