topic Re: Conversion?? in QlikView

Conversion??

qlikuser222 — Tue, 11 Jul 2017 14:49:17 GMT

Hi All,

I have a excel file where I am pulling numbers.The number format are R$ 222.00, R$ 222.998 etc

I am unable to do sum, but I can do count.

How to format the column in the backend so I can actually do the sum.

Thanks

sunny_talwar — Tue, 11 Jul 2017 14:51:07 GMT

May be this

Money#(FieldName, 'R$ #,###.###') as FieldName

Frank_Hartmann — Tue, 11 Jul 2017 14:55:22 GMT

or maybe this:

num#(mid(YourField,3)) as number

qlikuser222 — Tue, 11 Jul 2017 15:03:06 GMT

Hi ,

Thanks for reply.

I tried it but still not working .

PFA

Thanks

sunny_talwar — Tue, 11 Jul 2017 15:05:57 GMT

I don't see anything attached... but seems to be working for me

I see attached now

sunny_talwar — Tue, 11 Jul 2017 15:07:42 GMT

Try this

LOAD

Money#(Amount,'R$ #.###,###', ',', '.') as Amount

FROM

[Test.xls]

(biff, embedded labels, table is Sheet1$);

sunny_talwar — Tue, 11 Jul 2017 15:08:50 GMT

Or try this

SET MoneyThousandSep='.';

SET MoneyDecimalSep=',';

SET MoneyFormat='R$#,##0.00;(R$#,##0.00)';

LOAD Amount

FROM

[Test.xls]

(biff, embedded labels, table is Sheet1$);

qlikuser222 — Tue, 11 Jul 2017 15:10:06 GMT

Thanks