topic Re: Text to Numeric problem in QlikView

Text to Numeric problem

tmumaw — Tue, 08 Mar 2016 13:28:38 GMT

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
];

Re: Text to Numeric problem

sunny_talwar — Tue, 08 Mar 2016 13:38:36 GMT

FineAmt? Is it the same thing as Fine?

Re: Text to Numeric problem

tmumaw — Tue, 08 Mar 2016 13:43:21 GMT

Yes. I tried num(Fine) as FineAmt .

Re: Text to Numeric problem

tmumaw — Tue, 08 Mar 2016 13:44:27 GMT

when I sum Fine or FineAmt I get this:

Re: Text to Numeric problem

sunny_talwar — Tue, 08 Mar 2016 13:44:38 GMT

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

Re: Text to Numeric problem

Peter_Cammaert — Tue, 08 Mar 2016 13:45:31 GMT

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

Re: Text to Numeric problem

sunny_talwar — Tue, 08 Mar 2016 13:45:55 GMT

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

Re: Text to Numeric problem

tmumaw — Tue, 08 Mar 2016 13:52:39 GMT

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