Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
i have a column which has filed 'Amount' as followes:
Amount
80'899.15 |
37'787.46 |
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
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
KeepChar(Amount,'0123456789') as Amount
now use sum(Amount)
Hi,
you can also try this if you have other special characters in your field data.
SubField(SubField(SubField(SubField(SubField(Amount,'/'),'.'),'&'),'('),')') as Amount;
Hi,
Convert your Amount value from Text to number.
if it is solves your problem please mark it as correct answer.
just replace the sepecial thousand separator with nothig
num(replace (Amount,chr(39),'')) as Amount
Hi,
try this
LOAD * ,SubField(SubField(SubField(SubField(SubField(Amount,'/'),'.'),'&'),'('),')') as TEST_AMOUNT INLINE [
Amount
80'899.15
37'787.46
37'787.46
13'895.00
33'924.67
1'799.23
3'598.46
501'242.88
1'503'728.66
];
PurgeChar(Amount,Chr(39)) as Amount