topic Re: no identification for round brackets in csv in QlikView

no identification for round brackets in csv

maxsheva — Sat, 28 Jul 2018 19:39:14 GMT

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

Re: no identification for round brackets in csv

kfoudhaily — Sat, 28 Jul 2018 20:02:18 GMT

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,

Re: no identification for round brackets in csv

maxsheva — Sat, 28 Jul 2018 20:09:22 GMT

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)

Re: no identification for round brackets in csv

vishsaggi — Sun, 29 Jul 2018 03:42:55 GMT

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

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

Re: no identification for round brackets in csv

maxsheva — Sun, 29 Jul 2018 19:11:25 GMT

Thanks @vishsaggi

My expression is working well.

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

Thanks All.

Re: no identification for round brackets in csv

MarcoWedel — Mon, 30 Jul 2018 20:37:17 GMT

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

Re: no identification for round brackets in csv

maxsheva — Tue, 31 Jul 2018 19:30:48 GMT

Much appreciated Marco!

It is that what I expected to find.