Rounding a Calculated Field & Other Functions

Toad Data Point & Toad Intelligence Central
1

I have the following calculated field in the "Build Query" diagram:

RetenRate_CALC (TIMEPERIOD field not shown, but logic works) =
IF((COHORT_SUMS.TIMEPERIOD=2 OR
COHORT_SUMS.TIMEPERIOD=3 OR
COHORT_SUMS.TIMEPERIOD=4)
AND
COHORT_SUMS.Sum_ENR_UG_SAME_UNIT>0,

((COHORT_SUMS.Sum_ENR_UG_SAME_UNIT/COHORT_SUMS.COHORT_TOT)* 100), '')

Which produces the following results:

image

I was trying to round the RetenRat_CALC field to two decimal places. Nothing was working, so I widened the column of the calculated field, where it seems I was trying to ROUND() a string field where I would have thought (because of the calculations) it would have been numeric.

Therefore, what is the best way to:

  • convert a string field to numeric; is transform/cleans the only way to accomplish this?
    -- I need the 0 or <0 fields to be null.

  • Does double-single quotes nullify a field or is there a better way?

  • Is there a any difference between the functions in the "create calculated field" in the query builder verses the functions available in transform/cleans?

Thank you for any advice you can offer!

2

Hi Rochelle,
what about if you right-clicked the field caption and selected Value Field Settings - Selected Number Format, then Number and adjusted the number of decimal places?

3

Hi Dan - thank you for your suggestion!

I apologize (still new to Toad), but I'm not sure where the "field caption" you mentioned is; query, results, cleanse/transform? As a solution, I created a "formatting step" and added that workflow step to my workbook. It works.

I just need to be aware that a calculated field - with calculations - doesn't automatically imply a numeric field result. Spent way too many hours yesterday trying to fix a numeric field, that wasn't numeric! Gah!

Lesson learned!

Thank you again Dan!

4

While I see that you have a solution in place...

Quick thing to try is changing the false part to return either a zero or NULL instead of ''. Having it return a number or nothing for both the true and false could cause it to return a numeric data type instead of text. You'll know it worked when the RetenRate_CALC values are right-aligned in the results (left-aligned signals that it is a string).

Once it is returning numeric data type, return to your calculation and wrap the calculation in the true section with a ROUND() function. Example: ROUND(, 2) would round to 2 decimals.

5

Thank you! Yes, I finally realized the number was left justified, which is what cued me off that I was dealing with a string. The funny thing was is that I even included a 1* (multiple by one) to see if I could force the calculated field into being numeric - no luck though. So the lesson I learned was, if I null a field, the populated fields are string. Can't do null... have to force a zero, then the whole field is numeric.

Thanks again InfoMan for your reply ... I appreciate your suggestion & advice.