Return the string portion of a dual after the rounded average of a field

[#1]

I am working with financial security Ratings.  The field SP_dual is set up as

[1,AAA

2,AA

....

]

I am using minstr(sp_dual) successfully to return AAA based on the underlying numbers.  My problem is now that I want to run an average (or weighted avg) and return the closest string.  i.e. the Avg returns 1.1 and I want to return AAA.  I've tried using the round() function to get it to a number that should have a string value.  I've tried re-using the applymap function in the expression but it was only returning nulls.

any ideas?

[MayilVahanan]

Hi

Try with floor() function.

Note:

Can you provide a sample file too?

[#3]

I don't necessarily want to round down, but Floor() did not return the text of the underlying dual.  Please find attached a sample.  I have a nested pick(match()) statement that returns various dual() fields with minstr/maxstr.  But I need to also take an average calculation and still return the strings...essentially an avgstr() function.

[rustyfishbones]

Use SUBFIELD Function

SUBFIELD(SP_dual ,',' ,2)  as SP

[#5]

using subfield returns null.  When I explained the structure of the sp_dual, that was meant to be short hand for an Inline load statement.

[rustyfishbones]

Apologies Mark, I mis read your request

[#7]

Just wanting to re-raise this issue in the conciousness of the community.  How to convert a dual, after a round(avg()) back to the appropriate dual value, and not just the number.  I need to display the string that most closely matches the avg of several values.

[swuehl]

Both round() and avg() return numbers only, not duals.

So if you want to classify the number, you'll need to apply the appropriate logic again (for example using nested if() statements etc.).

Like

=if( round(avg(Dual)) < 1, 'smaller 1',

         if( round(avg(Dual)) <= 10, 'between 1 and 10', 'larger than 10' )

)

[#9]

So, because I have to perform round/avg calculations on the duals, there's no point in them being duals in the first place?  Or is there someway to use applymap() in the expression to reapply the dual mapping?  If not, It would seem that the use of duals does me no good.  Am I unfortunately correct in this assumption?

[jonathandienst]

Mark

You have raised an interesting point. Its easy enough to convert dates and numbers back to the dual type using Date() and Num(), but there is no function to do that for a custom dual value, which I use quite often.

In your case, using separate numbers and text associated in a table may be better than using duals.

I think there is a case for a NumToDual() function that would do that based on the existing dual values in the field. Nested if is not an attractive alternative as it requires hard coding of the dual which would not be good practice.

How about raising an idea for this?

Regards

Jonathan