Text to Numeric problem

[tmumaw]

Good morning everyone,

Having a little problem. I have the attached table and it appears it's treating the column labeled fine as a text field.  How can I be sure it's numeric and when used in an expression like sum(FineAmt) I get the correct answer?  I do a left join based on FineRangeID.  Thanks

//Fine_Range:
Left Join (Temp11)I
LOAD * INLINE [FineRangeID, FineRange, Fine
1, 0% to 9%, 5000,
2, 10% to 19%, 4000,
3, 20% to 29%, 3000,
4, 30% to 39%, 2500,
5, 40% to 49%, 2000,
6, 50% to 59%, 1500,
7, 60% to 69%, 1000,
8, 70% to 79%, 500,
9, 80% to 89%, 0,
10, 90% to 100%, 0
];

[sunny_talwar]

FineAmt? Is it the same thing as Fine?

[tmumaw]

Yes.  I tried num(Fine) as FineAmt .

[tmumaw]

when I sum Fine or FineAmt I get this:

[sunny_talwar]

What if you do Num#(Fine, '##') as FineAmt

[Peter_Cammaert]

Maybe you should omit the separators for column 4 (which doesn't exist). Drop the commas at the end of lines 1-9.

[sunny_talwar · Accepted solution]

Oh I get it:

Try this-> Sum(Aggr(Fine, FineRangeID)) or Sum(Aggr(Avg(Fine), FineRangeID))

UPDATE:
I think join is creating multiple Fine and when you do a straight sum you would see duplicating Fines all summing up

[tmumaw]

Thanks Sunny T.....the first AGGR worked.