topic Re: How can i sum the following column in QlikView

How can i sum the following column

Wed, 08 Jul 2015 10:29:19 GMT

i have a column which has filed 'Amount' as followes:

Amount

80'899.15

37'787.46

13'895.00

33'924.67

1'799.23

3'598.46

501'242.88

1'503'728.66

When is use sum function sum(Amount) it gives value 0

Can anyone one explain how to resolve this problem

Re: How can i sum the following column

awhitfield — Wed, 08 Jul 2015 10:32:51 GMT

I would hazard a guess that Qlikview thinks the values are TEXT due to the ' being used as a separator, remove the character in the load script and add it again using the number formatting.

Andy

Re: How can i sum the following column

arulsettu — Wed, 08 Jul 2015 10:35:37 GMT

KeepChar(Amount,'0123456789') as Amount

now use sum(Amount)

Re: How can i sum the following column

krishna20 — Wed, 08 Jul 2015 11:31:54 GMT

Hi,

The blow link may helps you

Remove characters before a special character

Re: How can i sum the following column

krishna20 — Wed, 08 Jul 2015 11:39:11 GMT

Hi,

you can also try this if you have other special characters in your field data.

SubField(SubField(SubField(SubField(SubField(Amount,'/'),'.'),'&'),'('),')') as Amount;

Re: How can i sum the following column

tyagishaila — Wed, 08 Jul 2015 12:05:25 GMT

Hi,

Convert your Amount value from Text to number.

Re: How can i sum the following column

arulsettu — Wed, 08 Jul 2015 12:18:25 GMT

if it is solves your problem please mark it as correct answer.

Re: How can i sum the following column

bumin — Wed, 08 Jul 2015 12:23:00 GMT

just replace the sepecial thousand separator with nothig

num(replace (Amount,chr(39),'')) as Amount

Re: How can i sum the following column

krishna20 — Wed, 08 Jul 2015 12:39:23 GMT

Hi,

try this

LOAD * ,SubField(SubField(SubField(SubField(SubField(Amount,'/'),'.'),'&'),'('),')') as TEST_AMOUNT INLINE [

Amount

80'899.15

37'787.46

13'895.00

33'924.67

1'799.23

3'598.46

501'242.88

1'503'728.66

];

Re: How can i sum the following column

sasiparupudi1 — Wed, 08 Jul 2015 12:50:11 GMT

PurgeChar(Amount,Chr(39)) as Amount