Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

qlikuser222
Contributor

Conversion??

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

1 Solution

Accepted Solutions

Re: Conversion??

Try this

LOAD

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

FROM

[Test.xls]

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

7 Replies
Highlighted

Re: Conversion??

May be this

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

Frank_Hartmann
Honored Contributor II

Re: Conversion??

or maybe this:

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

qlikuser222
Contributor

Re: Conversion??

Hi ,

Thanks for reply.

I tried it but still not working .

PFA

Thanks

Re: Conversion??

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

Capture.PNG

I see attached now

Re: Conversion??

Try this

LOAD

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

FROM

[Test.xls]

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

Re: Conversion??

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
Contributor

Re: Conversion??

Thanks