XSL v.1, Muenchian Grouping, Summing Line Items per Invoice, Call-Template

Question

I'm trying to sum the aggregate of line item amounts in a group with multiple groups. That is, there are many Invoices with many Line Items per Invoice. I need to sum the Amounts of Line Items across each Invoice.

I've searched across various posts here on Stack, as well as various forums, and haven't been able to decipher the code to be able to sum values with a Muenchian Grouping approach. If there is already a solution for this, please point me in the right direction.

The grouping occurs on the recordId attribute.

XML:

<?xml version="1.0" encoding="UTF-8"?>
<query>
    <results total="6">
        <result recordId="15918960" associatedRecordId="null" boId="10002385">
            <columns>
                <column>
                    <field>AmountNU</field>
                    <LI_Amount_display><![CDATA[$20.74]]></LI_Amount_display>
                </column>
            </columns>
        </result>
        <result recordId="15918960" associatedRecordId="null" boId="10002385">
            <columns>
                <column>
                    <field>AmountNU</field>
                    <LI_Amount_display><![CDATA[$30.74]]></LI_Amount_display>
                </column>
            </columns>
        </result>
        <result recordId="15918960" associatedRecordId="null" boId="10002385">
            <columns>
                <column>
                    <field>AmountNU</field>
                    <LI_Amount_display><![CDATA[$40.74]]></LI_Amount_display>
                </column>
            </columns>
        </result>
        <result recordId="15918961" associatedRecordId="null" boId="10002385">
            <columns>
                <column>
                    <field>AmountNU</field>
                    <LI_Amount_display><![CDATA[$20.74]]></LI_Amount_display>
                </column>
            </columns>
        </result>
        <result recordId="15918961" associatedRecordId="null" boId="10002385">
            <columns>
                <column>
                    <field>AmountNU</field>
                    <LI_Amount_display><![CDATA[$30.74]]></LI_Amount_display>
                </column>
            </columns>
        </result>
        <result recordId="15918962" associatedRecordId="null" boId="10002385">
            <columns>
                <column>
                    <field>AmountNU</field>
                    <LI_Amount_display><![CDATA[$29.74]]></LI_Amount_display>
                </column>
            </columns>
        </result>       
    </results>
</query>

XSL:

<?xml version="1.0" encoding="UTF-8"?>

<xsl:stylesheet xmlns:xsl="http://www.w3.org/1999/XSL/Transform" version="1.0"
xmlns:datetime="http://exslt.org/dates-and-times"
xmlns:exsl="http://exslt.org/common"
xmlns:fn="http://www.w3.org/2013/xpath-functions"
exclude-result-prefixes="datetime">
    <xsl:output method="text" encoding="UTF-8" indent="no"/>

    <xsl:key name="recordID" match="result" use="@recordId"/><!-- Define a key to use for grouping the results -->
    <!-- Might be needed for nulls 
    <xsl:template match="/results/result/columns/column/LI_Amount_display[not(text()[normalize-space()])]"> 
    <xsl:element name='LI_Amount_display' value="0.00"></xsl:element> 
    </xsl:template> -->    

    <xsl:template match="/">
        <xsl:call-template name="fixTheWidth" >
            <!-- This parameter is a Id for each group of records based on the result/@recordId attribute. This groups all records to the record ID-->
            <xsl:with-param name="resultIndex" select="//results/result[generate-id(.) = generate-id(key('recordID', @recordId)[1])]" />
        </xsl:call-template>
    </xsl:template>

    <xsl:template name="fixTheWidth" match="/results">
        <xsl:param name="resultIndex" /> <!-- A unique index based on grouping the records on the recordID -->

<!-- MORE CODE HERE USING $resultIndex has been redacted for simplicity-->

        <xsl:for-each select="$resultIndex" >
            <xsl:text> BEGIN | </xsl:text>
                <xsl:value-of select="number(translate(substring(key('recordID',@recordId)/columns/column/LI_Amount_display,2),',',''))"></xsl:value-of>
                <!-- <xsl:value-of select="sum(number(translate(substring(key('recordID',@recordId)/columns/column/LI_Amount_display,2),',','')))"></xsl:value-of>-->
        </xsl:for-each>   
         <xsl:text> | END  </xsl:text>


<!-- MORE CODE HERE USING $resultIndex has been redacted for simplicity-->


    </xsl:template>
</xsl:stylesheet>

I realize that using the "call-template" strategy is not necessarily the "best" approach, but its what I've come to such that I can create a fixed width flat file and not have to refactor the entire script. If there's a way to accomplish this otherwise, I'm all ears.

Answer 1

You cannot sum nodes that aren't numbers. A value of $20.74 is a string, not a number. You must first convert the values to numbers by removing the currency symbol (and any other non-numeric characters, if they are allowed in the input), then proceed to group the resulting nodes and sum the groups.

Here's an example:

XSLT 1.0

<xsl:stylesheet xmlns:xsl="http://www.w3.org/1999/XSL/Transform" version="1.0"
xmlns:exsl="http://exslt.org/common"
exclude-result-prefixes="exsl">
<xsl:output method="text" encoding="UTF-8"/>

<xsl:key name="amt" match="amount" use="@id"/>

<xsl:template match="/query">
    <xsl:variable name="amounts">
        <xsl:for-each select="results/result">
            <amount id="{@recordId}"><xsl:value-of select="translate(columns/column/LI_Amount_display, '$', '')"/></amount>
        </xsl:for-each>
    </xsl:variable>
    <xsl:variable name="amount-set" select="exsl:node-set($amounts)/amount" />

    <xsl:for-each select="$amount-set[generate-id() = generate-id(key('amt', @id)[1])]" >
        <xsl:text> BEGIN | </xsl:text>
        <xsl:value-of select="sum(key('amt', @id))"/>
    </xsl:for-each>   
    <xsl:text> | END  </xsl:text> 
</xsl:template>

</xsl:stylesheet>

Applied to your example input, the result is:

 BEGIN | 92.22 BEGIN | 51.48 BEGIN | 29.74 | END  

I couldn't understand the strategy of calling a template and "create a fixed width flat file". In any case, it seems unrelated to the question at hand. You might post a separate question, if necessary.