I was looking at the comments on my post about String Essbase CDF functions and found an odd request there.
I couldn’t find a reason why someone would try doing that. However I ending up creating 4 functions to perform the request!!!
The request was to create a String to Number function.
Let’s say that you want to convert some of your metadata to data in Essbase, there is no function that exists now which can do this. (I’m not sure whether there are some CDFs out there).
Let’s see how it works.
I took Sample Basic and created two members which will hold the integer and double values.
StringInteger and StringDouble
FIX(@RELATIVE("Product",0), "Jan", "New York", "Budget") "StringInteger" ( @StringtoInteger(@SUBSTRING(@NAME(@CURRMBR("Product")),4)) ; ) "StringDouble" ( @StringtoDouble(@CONCATENATE(@CONCATENATE(@SUBSTRING(@NAME(@CURRMBR("Product")),0,3),"."),@SUBSTRING(@NAME(@CURRMBR("Product")),4))) ; ) ENDFIX
For the next two functions, you can pass the replacement character (for example product name contains “-” in it and I would like to remove it and store the numbers)
StringDoublewReplace and StringIntegerwReplace members were added to hold these numbers.
FIX(@RELATIVE("Product",0), "Jan", "New York", "Budget") "StringDoublewReplace" ( @StringtoDoublewReplace (@NAME(@CURRMBR("Product")),"-"); ) "StringIntegerwReplace" ( @StringtoIntegerwReplace (@NAME(@CURRMBR("Product")),"-"); ) ENDFIX
You can register the function using the below given MaxL statements.
CREATE or REPLACE FUNCTION '@StringtoInteger' AS 'com.cera.hyperionUtils.HypStringtoInt.strtoint(String)' SPEC '@StringtoInteger(String)' COMMENT 'Convert String to Integer @StringtoInteger("100") will give you 100 as an integer'; CREATE or REPLACE FUNCTION '@StringtoDouble' AS 'com.cera.hyperionUtils.HypStringtoDouble.strtodouble(String)' SPEC '@StringtoDouble(String)' COMMENT 'Convert String to Double @StringtoDouble("100.10") will give you 100.10 as a Double'; CREATE or REPLACE FUNCTION '@StringtoDoublewReplace' AS 'com.cera.hyperionUtils.HypStringtoDoublewReplace.strtodoublewrplc(String,String)' SPEC '@StringtoDoublewReplace(String,String)' COMMENT 'Convert String to Double with replacement option @StringtoDoublewReplace("100-10","-") will give you 100.10 as a Double'; CREATE or REPLACE FUNCTION '@StringtoIntegerwReplace' AS 'com.cera.hyperionUtils.HypStringtoIntwReplace.strtointwrplc(String,String)' SPEC '@StringtoIntegerwReplace(String,String)' COMMENT 'Convert String to Integer with replacement option @StringtoIntegerwReplace("100-10","-") will give you 10010 as an Integer';
You can download the CDF from here.
For installation instructions please look here.
Hi Celvin,
StringInteger & StringDouble act as same like Attribute calculation functions rite !!! and in terms of replacement of "-" at any point of time are we defining against which dimension the replacement has to occur ? what if i have my scenario member as "Actual-V1" in the retrieval apart from 100-10 product.
Thanks
Amith
Amit
Yes the replacement is for the dimension. So I did the replacement for Product (see @CURRMBR)
Hi, I'm trying to get CDF but seems that files aren't available for download
It is there, I checked right now. Maybe your firm is blocking the download site
If you are on the latest Calc Manager patch, you don't need this CDF. There is one in built CDF with CalcManager @CalcMgrDoublefromString
I am trying to link a data value to an alpha-numeric string. Example: 30.0 (data value of a member) to ABC_30.0 (member name). I’ve created the member @StringtoInteger via maxl. Also updated the udf.policy. Also placed jar file in the essbase java home directory. Am I missing any steps?
In addition, can you pls tell me where I can find more info on creating this via Calc Manager? We’re on 11.1.2.4.0.79. I can’t find any additional info regarding @CalcMgrDoublefromString command that you’ve mentioned below. Thanks!
You don’t need that anymore.
You can use @Concatenate(“ABC_”,@CalMgrDoubletoString(member which has 30))
This will generate it as ABC_30.
I have a calc that generates data to a date typed measure member. I’m performing a second calc which i need to pull the month and year from that date (presumably using DP_MONTH and DP_YEAR) for a cross dim reference for month and year dims, however these functions result in a numerical value instead of a string. What is the best way to convert these values to a string?