How to create a composite key for muenchian grouping

Question

These are just three records of my client's xml, but it shows the problem:

<Export>
    <Record>
        <JournalX>01-252-5390</JournalX>
        <PositionX></PositionX>
        <DepartmentX></DepartmentX>
        <Description><![CDATA[UK - Take The Cash]]></Description>
        <Amount>116.66</Amount>
        <PayDate>06/30/2022</PayDate>
        <PPEndDate>06/30/2022</PPEndDate>
    </Record>
    <Record>
        <JournalX>5200</JournalX>
        <PositionX></PositionX>
        <DepartmentX>262</DepartmentX>
        <Description><![CDATA[UK - Salary]]></Description>
        <Amount>3655.92</Amount>
        <PayDate>06/30/2022</PayDate>
        <PPEndDate>06/30/2022</PPEndDate>
    </Record>
        <Record>
        <JournalX>5200</JournalX>
        <PositionX>311</PositionX>
        <DepartmentX>310</DepartmentX>
        <Description><![CDATA[UK - Salary]]></Description>
        <Amount>1424.99</Amount>
        <PayDate>06/30/2022</PayDate>
        <PPEndDate>06/30/2022</PPEndDate>
    </Record>
</Export>

What needs to happen is to sum Amount by Description and AcctNo. As you can see there is not AcctNo in the XML. This is how I created an AcctNo variable:

<xsl:variable name="AcctNo">
    <xsl:choose>
        <xsl:when test="string-length(JournalX) > 4">
            <xsl:value-of select="JournalX" />
        </xsl:when>
        <xsl:when test="string-length(PositionX) != 3 or PositionX = '' ">
            <xsl:value-of select="concat('01-', DepartmentX, '-', JournalX)" />
        </xsl:when>
        <xsl:otherwise>
            <xsl:value-of select="concat('01-', PositionX, '-', JournalX)"/>
        </xsl:otherwise>
    </xsl:choose>
</xsl:variable>

Alternatively, I created an AcctNo function in the CData section, basically the same algorithm. But the problem with either of these, is that I have to create the Key outside the template definition and the variable is defined within the template, therefore not available to the variable.

An additional problem, at least I think it may be, is that they want the output to be csv.

Can anyone help me? Thank You so much, Greg

I have copied code I found on the web and tried to modify it which is how I found out about the limitation.

Any help would be really appreciated.

Answer 1

Using XSLT 3 with

<xsl:stylesheet xmlns:xsl="http://www.w3.org/1999/XSL/Transform"
    xmlns:xs="http://www.w3.org/2001/XMLSchema"
    exclude-result-prefixes="#all"
    version="3.0">

  <xsl:output method="text" indent="yes" html-version="5"/>

  <xsl:template match="/Export">
    <xsl:for-each-group select="Record" composite="yes" group-by="Description, if (string-length(JournalX) > 4) then JournalX else if (string-length(PositionX) != 3 or PositionX = '') then concat('01-', DepartmentX, '-', JournalX) else concat('01-', PositionX, '-', JournalX)">
      <xsl:value-of select="sum(current-group()/Amount)"/>
      <xsl:text>&#10;</xsl:text>
    </xsl:for-each-group>
  </xsl:template>
  
</xsl:stylesheet>

I get

116.66
3655.92
1424.99

using XSLT 1.0 with a two step transformation I get the same result

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

  <xsl:output method="text" indent="yes" />

  <xsl:key name="group-by-desc-and-acctno" match="Record" use="concat(Description, '|', AcctNo)"/>

  <xsl:template match="/Export">
    <xsl:variable name="records-with-added-acctno-rtf">
      <xsl:apply-templates select="Record"/>
    </xsl:variable>
    <xsl:variable name="records-with-added-acctno" select="exsl:node-set($records-with-added-acctno-rtf)/Record"/>
    <xsl:for-each select="$records-with-added-acctno[generate-id() = generate-id(key('group-by-desc-and-acctno', concat(Description, '|', AcctNo))[1])]">
      <xsl:value-of select="sum(key('group-by-desc-and-acctno', concat(Description, '|', AcctNo))/Amount)"/>
      <xsl:text>&#10;</xsl:text>
    </xsl:for-each>
  </xsl:template>
  
  <xsl:template match="Record">
    <xsl:copy>
      <xsl:copy-of select="*"/>
      <AcctNo>
        <xsl:choose>
            <xsl:when test="string-length(JournalX) > 4">
                <xsl:value-of select="JournalX" />
            </xsl:when>
            <xsl:when test="string-length(PositionX) != 3 or PositionX = '' ">
                <xsl:value-of select="concat('01-', DepartmentX, '-', JournalX)" />
            </xsl:when>
            <xsl:otherwise>
                <xsl:value-of select="concat('01-', PositionX, '-', JournalX)"/>
            </xsl:otherwise>
        </xsl:choose>        
      </AcctNo>
    </xsl:copy>
  </xsl:template>

</xsl:stylesheet>