Conversion??

[qlikuser222]

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]

May be this

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

[Frank_Hartmann]

or maybe this:

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

[qlikuser222]

Hi ,

Thanks for reply.

I tried it but still not working .

PFA

Thanks

[sunny_talwar]

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

I see attached now

[sunny_talwar · Accepted solution]

Try this

LOAD

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

FROM

[Test.xls]

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

[sunny_talwar]

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]

Thanks