Count a string field that contains a numeric value 1

[cjbrown815]

Hi,
I need to tally (count) all results greater then 99. The field is a string (SAMPLEPARAM.SRESULT)and it contains numeric values like <10, <1, >150, and whole numbers. I need to tally everything greater then 99.

I have color coding as well. If the value is greater than 99 it turns yellow and greater then 999 its red.

thanks

-CJ

SQL2005// CRXIr2// XP Pro

"Progress lies not in enhancing what is, but in advancing toward what will be"
-KHALIL GIBRAN 1883-1931

 

[Charliy]

What value would you want to tally for the entries with a > or <?

Let's say you had <100, would you count that as 99? What about >50 - that could be anything from 51 to the limit of you field size, including many vales above 99.

If you're just going to count the highest value for < (<50 = 49) and the lowest for > (> 98 = 99) then you could try something like this:

local numbervar nval;
if isnumeric {table.field}
then nval := val({table.field}
else
if left({table.field,1) = '<'
then nval := val(mid(table.field},2)-1
else
if left({table.field,1) = '>'
then nval := val(mid(table.field},2)+1
else
nval := 0;
if nval < 99
then nval:=0
else nval:=nval

 

[fisheromacse]

It may be possible to use a running total, but with your field being a string and not numeric it may not be any easier.
maybe something like this:

create a formula
//{@Eval}
IF isnumeric({SAMPLEPARAM.SRESULT})=TRUE then tonumber({SAMPLEPARAM.SRESULT})
else
IF isnumeric(MID({SAMPLEPARAM.SRESULT},2))=TRUE then tonumber(MID({SAMPLEPARAM.SRESULT},2))
else 0;


create a second formula
//{@CountEval}
numbervar g99;
IF {@Eval} < 99 then g99 := g99 else g99 := G99 +1;


and create a third formula
//{@DisplayEval}
numbervar g99;
g99




Once you get the Eval formula working, you can use it's results in your color coding formula by right clicking the formula in the report, selecting Format Field, then under the Border tab, set the color using background.

 

[cjbrown815]

Data sample

RESULT over 99
>150 1
<10 0
496 1
5 0
101 1
65 1

TOTAL OVER 99 = 4

I need to convert the string and then count all values over 99, thanks

-CJ

SQL2005// CRXIr2// XP Pro

"Progress lies not in enhancing what is, but in advancing toward what will be"
-KHALIL GIBRAN 1883-1931

 

[cjbrown815]

OK, so the format didnt work, the results are >150, <10, 496, 5, 101, 65.
The "Over 99" column was supposed to show a 1 or zero based on the result, its just flagging everything over 99 so I can count it, thanks



-CJ

SQL2005// CRXIr2// XP Pro

"Progress lies not in enhancing what is, but in advancing toward what will be"
-KHALIL GIBRAN 1883-1931

 

[pmax9999]

I would use the following formula to trim out the "<", ">" and any spaces, and use the result as the basis on which to count (or use the code in a Running Total formula):

Val(Trim(Replace(Replace({SAMPLEPARAM.SRESULT}, '<', ''), '>', '')))

Cheers
Pete

 

[Charliy]

change the last line of my original code to: else nval := 1

This formula will then give you a 1 if the value is 99 or greater, and 0 if it is not.

You can them do a sum of that formula.

 

[cjbrown815]

Thanks for the help. If the value is > xx then it could be a million, if its < xx then its assumed to be 0. In science we cannot report 0 because there's always a chance something undetectable is still present. The greater sign is used because its the most we can count per the scenario and it is reported that the upper limit has been breached. Hope this clarifies

-CJ

SQL2005// CRXIr2// XP Pro

"Progress lies not in enhancing what is, but in advancing toward what will be"
-KHALIL GIBRAN 1883-1931

 

[Charliy]

Okay that changes the rules. So if it was >5 you would want to count it?

 

[cjbrown815]

yes, anything with a greater sign would be over 99

-CJ

SQL2005// CRXIr2// XP Pro

"Progress lies not in enhancing what is, but in advancing toward what will be"
-KHALIL GIBRAN 1883-1931

 

[Charliy]

That makes it easy.

if isnumeric( {table.field}) and val{table.field) > 99
then 1
else
if left({table.field},1) = '>'
then 1
else 0


Then just sum that formula.

 

[cjbrown815]

Charliy, that worked!! thanks, I just had to close the val with a (


thanks again!!

-CJ

SQL2005// CRXIr2// XP Pro

"Progress lies not in enhancing what is, but in advancing toward what will be"
-KHALIL GIBRAN 1883-1931