Clarity

Expand all | Collapse all

[SOLVED] Number Formatting and String Manipulation

  • 1.  [SOLVED] Number Formatting and String Manipulation

    Posted Aug 27, 2012 04:03 AM
    Hi!

    Is there any why to define the number formatting or at least to manipulate a string?

    My problem is the following one: even if I have a filed on salesforce db without decimals (no floating points), the query always
    returns me the number followed by .0. Since I am not able to fix this on salesforce I would like to manipulate the number formating or the string on Clairty.

    Let say I would like to say "remove all decimals" or "split string on '.' "

    Is it possible to do that on a GEL script?

    thanks in advance
    giacomo


  • 2.  RE: Number Formatting and String Manipulation

    Posted Aug 27, 2012 04:09 AM
    Not answering you question directly, but when I have some similar issue in a query based portlet I usually do a datatype transformation in the SQL part of the query.


    Martti K.


  • 3.  RE: Number Formatting and String Manipulation

    Posted Aug 27, 2012 04:25 AM
    Yes, you can. Gel / jelly is a shell on top of java, remember, so all your variables are actually java types underneath. If its got decimals its probably a long, which you can convert to an integer (without decimals) using:
    <j:set var="newVariable" value="${new java.lang.Long(nameOfYourVariable).intValue()}" />


  • 4.  RE: Number Formatting and String Manipulation

    Posted Aug 27, 2012 05:52 AM
    thanks mark!

    it looks great, the only issue is that while executing it returns the following error:
    org.apache.commons.jelly.expression.jexl.JexlExpressionFactory.createExpression(JexlExpressionFactory.java:60) ... 22 more Root cause org.apache.commons.jelly.JellyException: null:-1:-1: <null> Unable to create expression: new java.lang.Long(r_eval__c).intValue() at org.apache.commons.jelly.parser.XMLParser.creat...
    note:
    I added this at top of the script:
    xmlns:j="jelly:core"

    and I also tried using different syntax for the variable ($var, ${var})...and use String instead of Int...


  • 5.  RE: Number Formatting and String Manipulation

    Posted Aug 27, 2012 06:18 AM
    One more error using Floating Point...any way to fix this error!?
    BPM-0704: Si è verificato un errore durante l'esecuzione dello script personalizzato: org.apache.commons.jelly.JellyException: null:-1:-1: <null> Could not parse Jelly script at org.apache.commons.jelly.JellyContext.compileScript(JellyContext.java:547) at com.niku.union.gel.GELScript.run(GELScript.java:46) at com.niku.union.gel.GELController.invoke(GELController.java:20) at com.niku.bpm.services.ExecuteCustomAction.run(ExecuteCustomAction.java:178) at java.util.concurrent.ThreadPoolExecutor$Worker.runTask(ThreadPoolExecutor.java:886) at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:908) at java.lang.Thread.run(Thread.java:619) Caused by: org.apache.commons.jelly.JellyException: null:-1:-1: <null> Unable to create expression: new java.lang.Float(4.5f).intValue() at org.apache.commons.jelly.parser.XMLParser.createSAXException(XMLParser.java:1180) at org.apache.commons.jelly.parser.XMLParser.createSAXException(XMLParser.java:1202) at org.apache.commons.jelly.parser.XMLParser.createTag(XMLParser.java:1036) at org.apache.commons.jelly.parser.XMLParser.startElement(XMLParser.java:593) at org.apache.xerces.parsers.AbstractSAXParser.startElement(Unknown Source) at org.apache.xerces.parsers.AbstractXMLDocumentParser.emptyElement(Unknown Source) at org.apache.xerces.impl.XMLNSDocumentScannerImpl.scanStartElement(Unknown Source) at org.apache.xerces.impl.XMLDocumentFragmentScannerImpl$FragmentContentDispatcher.dispatch(Unknown Source) at org.apache.xerces.impl.XMLDocumentFragmentScannerImpl.scanDocument(Unknown Source) at org.apache.xerces.parsers.XML11Configuration.parse(Unknown Source) at org.apache.xerces.parsers.XML11Configuration.parse(Unknown Source) at org.apache.xerces.parsers.XMLParser.parse(Unknown Source) at org.apache.xerces.parsers.AbstractSAXParser.parse(Unknown Source) at org.apache.xerces.jaxp.SAXParserImpl$JAXPSAXParser.parse(Unknown Source) at org.apache.commons.jelly.parser.XMLParser.parse(XMLParser.java:254) at org.apache.commons.jelly.JellyContext.compileScript(JellyContext.java:543) ... 6 more Caused by: org.apache.commons.jelly.JellyException: null:-1:-1: <null> Unable to create expression: new java.lang.Float(4.5f).intValue() at org.apache.commons.jelly.expression.jexl.JexlExpressionFactory.createExpression(JexlExpressionFactory.java:64) at org.apache.commons.jelly.expression.CompositeExpression.parse(CompositeExpression.java:82) at org.apache.commons.jelly.TagLibrary.createExpression(TagLibrary.java:120) at org.apache.commons.jelly.parser.XMLParser.createTag(XMLParser.java:1016) ... 19 more Caused by: org.apache.commons.jexl.parser.ParseException: Encountered "java" at line 1, column 5. Was expecting one of: "||" ... "or" ... "&&" ... "and" ... "|" ... "^" ... "&" ... "==" ... "eq" ... "!=" ... "ne" ... "<" ... "lt" ... ">" ... "gt" ... "<=" ... "le" ... ">=" ... "ge" ... "+" ... "-" ... "*" ... "/" ... "div" ... "%" ... "mod" ... ";" ... "[" ... "." ... "=" ... at org.apache.commons.jexl.parser.Parser.generateParseException(Parser.java:3274) at org.apache.commons.jexl.parser.Parser.jj_consume_token(Parser.java:3158) at org.apache.commons.jexl.parser.Parser.ExpressionExpression(Parser.java:1549) at org.apache.commons.jexl.parser.Parser.Statement(Parser.java:1522) at org.apache.commons.jexl.parser.Parser.JexlScript(Parser.java:58) at org.apache.commons.jexl.parser.Parser.parse(Parser.java:17) at org.apache.commons.jexl.ExpressionFactory.createNewExpression(ExpressionFactory.java:116) at org.apache.commons.jexl.ExpressionFactory.createExpression(ExpressionFactory.java:94) at org.apache.commons.jelly.expression.jexl.JexlExpressionFactory.createExpression(JexlExpressionFactory.java:60) ... 22 more Root cause org.apache.commons.jelly.JellyException: null:-1:-1: <null> Unable to create expression: new java.lang.Float(4.5f).intValue() at org.apache.commons.jelly.parser.XMLParser.createSAXException(XMLParser.jav..


  • 6.  RE: Number Formatting and String Manipulation

    Posted Aug 27, 2012 06:22 AM
    OK, I was typing from memory, I guess I should try things out before I post. Give me a minute...


  • 7.  RE: Number Formatting and String Manipulation

    Posted Aug 27, 2012 06:31 AM
    OK, this actually works:
      <j:set var="longThing" value="1234.8" />
      <j:new className="java.lang.Float" var="tempFloat">
        <j:arg value="${longThing}" type="float" />
      </j:new>
      <j:set var="intThing" value="${tempFloat.intValue()}" />
    Obviously the first variable, longThing, is the one you already have, you don't need to create it yourself like I do here.


  • 8.  RE: Number Formatting and String Manipulation

    Posted Aug 27, 2012 01:06 PM
    Definitely perfect :)!
    Thanks a lot!!


  • 9.  RE: Number Formatting and String Manipulation

    Posted Dec 03, 2019 02:18 AM
    Thanks, it works perfectly also for me.


  • 10.  RE: Number Formatting and String Manipulation

    Posted Aug 27, 2012 06:19 AM
    Its just nameOfVariable, not ${nameOfVariable) or $nameOfVariable.  Its already inside an expression so you don't need to escape it again.


  • 11.  RE: Number Formatting and String Manipulation

    Posted Aug 27, 2012 06:50 AM
    Yes I did it already. I just tested adding again quotation marks and dollar only because it did not work.
    The problem is that it s says "it is not able to create expression...." looks like java does not work... (p.s. I have the SAAS version...could it be a problem!?)


  • 12.  RE: Number Formatting and String Manipulation
    Best Answer

    Posted Aug 27, 2012 07:18 AM
    It might not be quite as simple to setup/call compared to using a line of embedded java, but if your response from the force.com platform is an XML document/fragment, you could use XSLT.

    Here's a contrived example with all the source/demo data embedded directly into the script so that it can be tried out for yourself:
    <gel:script 
      xmlns:gel="jelly:com.niku.union.gel.GELTagLibrary"
      xmlns:x="jelly:org.apache.commons.jelly.tags.xml.XMLTagLibrary">
    
    <!-- sample XML data to play with -->
    <gel:parse var="xog_data">
    <NikuDatabus attribute="value">
      <Header object="test" />
      <Record code="uniqueval">
        <CustomInformation>
          <CustomColumnValue name="partition_code">NIKU.ROOT</CustomColumnValue>
          <CustomColumnValue name="formatme">8.0</CustomColumnValue>
          <CustomColumnValue name="formatme2">12348.3</CustomColumnValue>
          <CustomColumnValue name="formatme3">12348.3</CustomColumnValue>
          <CustomColumnValue name="formateu">633412348.4</CustomColumnValue>
        </CustomInformation>
      </Record>
      <XOGOutput>
        <SomeElements someAttribute="somevalue" />
      </XOGOutput>
    </NikuDatabus>
    </gel:parse>
    
    <!-- XSL Transformation templates -->
    <gel:parse var="xog_xsl">
    <xsl:stylesheet xmlns:xsl="http://www.w3.org/1999/XSL/Transform" version="1.0">
    <!-- identity template -->
    <xsl:template match="@*|node()">
       <xsl:copy>
          <xsl:apply-templates select="@*|node()"/>
       </xsl:copy>
    </xsl:template>
    
    <!-- removal template -->
    <xsl:template match="XOGOutput" />
    
    <!-- number formatting templates -->
    <xsl:decimal-format name="european" decimal-separator="," grouping-separator="."/>
     
    <xsl:template match="CustomColumnValue[@name='formatme' or @name='formatme2']/text()">
     <xsl:value-of select="format-number(., '###.#')"/>
    </xsl:template>
    
    <xsl:template match="CustomColumnValue[@name='formatme3']/text()">
     <xsl:value-of select="format-number(., '#')"/>
    </xsl:template>
    
    <xsl:template match="CustomColumnValue[@name='formateu']/text()">
     <xsl:value-of select="format-number(., '.###,#', 'european')"/>
    </xsl:template>
     
    </xsl:stylesheet>
    </gel:parse>
    
    <gel:set asString="true" select="$xog_data" var="before" />
    
    <!-- re-parse from GEL to XML library variable format -->
    <x:parse var="xog_xsl"><gel:include select="$xog_xsl" /></x:parse>
    <x:parse var="xog_data"><gel:include select="$xog_data" /></x:parse>
    
    <!-- transform and catch back to GEL variable, here is where the work takes place-->
    <gel:parse var="xog_corrected">
      <x:transform xml="${xog_data}" xslt="${xog_xsl}" />
    </gel:parse>
    
    <!-- showing the effect -->
    <gel:set asString="true" select="$xog_corrected" var="after" />
    <gel:out>Before: &#xA; ${before}</gel:out>
    <gel:out>After: &#xA; ${after}</gel:out>
    
    </gel:script>
    Produces this result when ran:
    C:\Users\Topdog\Downloads\GEL>gel -script xslt.gel
    
    Warning: JAVA_HOME environment variable is not set.
    
    Before:
     <?xml version="1.0" encoding="UTF-8"?>
    <NikuDatabus attribute="value">
        <Header object="test"/>
        <Record code="uniqueval">
            <CustomInformation>
                <CustomColumnValue name="partition_code">NIKU.ROOT</CustomColumnValue>
                <CustomColumnValue name="formatme">8.0</CustomColumnValue>
                <CustomColumnValue name="formatme2">12348.3</CustomColumnValue>
                <CustomColumnValue name="formatme3">12348.3</CustomColumnValue>
                <CustomColumnValue name="formateu">633412348.4</CustomColumnValue>
            </CustomInformation>
        </Record>
        <XOGOutput>
            <SomeElements someAttribute="somevalue"/>
        </XOGOutput>
    </NikuDatabus>
    
    After:
     <?xml version="1.0" encoding="UTF-8"?>
    <NikuDatabus attribute="value">
        <Header object="test"/>
        <Record code="uniqueval">
            <CustomInformation>
                <CustomColumnValue name="partition_code">NIKU.ROOT</CustomColumnValue>
                <CustomColumnValue name="formatme">8</CustomColumnValue>
                <CustomColumnValue name="formatme2">12348.3</CustomColumnValue>
                <CustomColumnValue name="formatme3">12348</CustomColumnValue>
                <CustomColumnValue name="formateu">633.412.348,4</CustomColumnValue>
            </CustomInformation>
        </Record>
    </NikuDatabus>
    
    
    C:\Users\Topdog\Downloads\GEL>
    The European formatting with thousands separator is just there for demonstration; if you wanted to keep it understood as a numeric format as you continue working with the XML, stick to '.' for decimal separation and no thousands separator character. You can also convert from a European formatted number string to an XML numeric with a little more work also.

    http://www.w3schools.com/xsl/func_formatnumber.asp

    Two other templates exist in this XSLT demo, which are staple templates for many transforms; first the identity template, which is an 'if nothing else needs to be done, copy from source to destination' action otherwise you'd have a fairly empty document at the end, and then a removal template to optionally discard elements/fragments entirely if desired. There are functions for string manipulation available too, for when that is needed.


  • 13.  RE: Number Formatting and String Manipulation

    Posted Aug 27, 2012 07:20 AM
    Apart from my home machine liking to create double-posts, the original script had a typo on line 45 where it was missing a closing apostrophe quote. Edited to fix.


  • 14.  RE: Number Formatting and String Manipulation

    Posted Aug 27, 2012 10:11 AM
    Thanks a lot for your reply...

    the xslt works fine on your example but while applying on my script I see an error:

    my input:
    <soapenv:Envelope xmlns:soapenv="http://schemas.xmlsoap.org/soap/envelope/" xmlns="urn:enterprise.soap.sforce.com" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:sf="urn:sobject.enterprise.soap.sforce.com">
       <soapenv:Body>
          <queryResponse>
             <result>
                <done>true</done>
                <queryLocator xsi:nil="true"/>
                <records xsi:type="sf:Account">
                   <sf:Id>001Q000000XCdx9IAD</sf:Id>
                   <sf:Fatturato__c>44994.0</sf:Fatturato__c>
                   <sf:Mercato__c>Corporate Captive</sf:Mercato__c>
                   <sf:Name>RossiSRL</sf:Name>
                   <sf:Nazione__c>Italia</sf:Nazione__c>
                   <sf:Nr_Progetti_Attivabili__c>3</sf:Nr_Progetti_Attivabili__c>
                   <sf:Potenziale_Sviluppo_Cliente__c>true</sf:Potenziale_Sviluppo_Cliente__c>
                   <sf:Rating_del_Paese__c>1</sf:Rating_del_Paese__c>
                   <sf:Sottocategoria__c>Vendita auto</sf:Sottocategoria__c>
                   <sf:Spending__c>45995.0</sf:Spending__c>
                </records>
                <records xsi:type="sf:Account">
                   <sf:Id>001Q000000XNcYSIA1</sf:Id>
                   <sf:Fatturato__c>123423.0</sf:Fatturato__c>
                   <sf:Mercato__c>P.A.</sf:Mercato__c>
                   <sf:Name>Pippo</sf:Name>
                   <sf:Nazione__c>Italia</sf:Nazione__c>
                   <sf:Nr_Progetti_Attivabili__c>4</sf:Nr_Progetti_Attivabili__c>
                   <sf:Potenziale_Sviluppo_Cliente__c>true</sf:Potenziale_Sviluppo_Cliente__c>
                   <sf:Rating_del_Paese__c>2</sf:Rating_del_Paese__c>
                   <sf:Spending__c>9876.0</sf:Spending__c>
                </records>
                <records xsi:type="sf:Account">
                   <sf:Id>001Q000000W5sOUIAZ</sf:Id>
                   <sf:Fatturato__c>7.48395655E8</sf:Fatturato__c>
                   <sf:Mercato__c>Corporate Privato</sf:Mercato__c>
                   <sf:Name>sorin spa</sf:Name>
                   <sf:Nazione__c>Italia</sf:Nazione__c>
                   <sf:Nr_Progetti_Attivabili__c>4</sf:Nr_Progetti_Attivabili__c>
                   <sf:Potenziale_Sviluppo_Cliente__c>true</sf:Potenziale_Sviluppo_Cliente__c>
                   <sf:Rating_del_Paese__c>1</sf:Rating_del_Paese__c>
                   <sf:Spending__c>748494.0</sf:Spending__c>
                </records>
                <size>3</size>
             </result>
          </queryResponse>
       </soapenv:Body>
    </soapenv:Envelope>
    I need to convert <sf:Fatturato__c> and <sf:Spending__c>.
    I used the same xslt you gave me with some modifications:
    <gel:parse var="xog_xsl">
    <xsl:stylesheet xmlns:xsl="http://www.w3.org/1999/XSL/Transform" version="1.0">
    <!-- identity template -->
    <xsl:template match="@*|node()">
       <xsl:copy>
          <xsl:apply-templates select="@*|node()"/>
       </xsl:copy>
    </xsl:template>
    
    <!-- removal template -->
    <xsl:template match="XOGOutput" />
    
    <!-- number formatting templates -->
    <xsl:decimal-format name="european" decimal-separator="," grouping-separator="."/>
     
    <xsl:template match="sf:Fatturato__c/text()">
     <xsl:value-of select="format-number(., '###.#')"/>
    </xsl:template>
    
    <xsl:template match="sf:Fatturato__c/text()">
     <xsl:value-of select="format-number(., '#')"/>
    </xsl:template>
    
    <xsl:template match="sf:Fatturato__c/text()">
     <xsl:value-of select="format-number(., '.###,#', 'european')"/>
    </xsl:template>
     
    </xsl:stylesheet>
    </gel:parse>
    And it is invoked in this way while iterating on the <records>:
    <gel:forEach select="$accounts//sforce:result/sforce:records" var="this_record">
    ...
    <gel:set var="r_id" select="$this_record/sf:Id/text()" asString="true"/>
    
    
    
    
    
    <gel:set var="r_name" select="$this_record/sf:Name/text()" asString="true"/>
    
    
    
    
    
    <gel:set var="r_industry" select="$this_record/sf:Industry/text()" asString="true"/>
    
    
    
    
    
    <gel:set var="r_Nr_Progetti_Attivabili__c" select="$this_record/sf:Nr_Progetti_Attivabili__c/text()" asString="true"/>
    
    
    
    
    
    <gel:set var="r_Fatturato__c" select="$this_record/sf:Fatturato__c/text()" asString="true"/>
    
    
    
    
    
    <gel:set var="r_Spending__c" select="$this_record/sf:Spending__c/text()" asString="true"/>
    ...
    <gel:parse var="xog_corrected">
    
    
    
    
    
    
    <x:transform xml="${this_record}" xslt="${xog_xsl}" />
    
    
    
    
    
    </gel:parse>
    
    
    
    
    
    <gel:set asString="true" select="$xog_corrected" var="out" />
    
    
    
    
    
    <gel:log>After: &#xA; ${out}</gel:log>
    Where is the error?

    please help me!


  • 15.  RE: Number Formatting and String Manipulation

    Posted Aug 27, 2012 02:26 PM
    There's no error text included to comment on. Did you leave that out?

    However, I do see that in the XSLT you've defined the same matching template 3 times, only the first match will be used the other 2 will be ignored.

    Note that XSLT works across the entire XML document in one go, you don't (need to) recursively call it in a loop over individual elements/values - whilst it may be made to work, it's inefficient that way and an unnatural way to implement it.

    [ Edit: Concern over scientific notation numbers removed, the XSLT processor is handling them fine. ]

    The last thing I can comment on, is that your XSLT is making use of namespaces that haven't been defined/declared inside the XSL file/document. That is likely a problem as it won't match/find the data.

    I think from your last post that you have the embedded java version working now though anyway? XSLT will take you some time to become familiar with.