XSLT Multiple Grouping

Question

would like to know how to go about creating multiple groupings in XSLT. The following XML should be grouped by memberID, deposit date, and deposit type. The total amount for each member should be summed by date and deposit type.

   <?xml version="1.0" encoding="UTF-8"?>
<Report>
<Deposit_Data>
 <MemberID>12345</MemberID>
    <MemberFirstName>Mickey</MemberFirstName>
    <MemberLastName>Mouse</MemberLastName>      
         <DepositDate>2023-11-22</DepositDate>
         <DepositInfo>
             <DepositType>CHK</DepositType>
                 <Amount>50.00</Amount>
         </DepositInfo>
         <DepositInfo>
             <DepositType>SAV</DepositType>
                 <Amount>30.00</Amount>
         </DepositInfo>
</Deposit_Data>
<Deposit_Data>
    <MemberID>12345</MemberID>
    <MemberFirstName>Mickey</MemberFirstName>
    <MemberLastName>Mouse</MemberLastName>      
            <DepositDate>2023-11-22</DepositDate>
            <DepositInfo>
                <DepositType>CHK</DepositType>
                    <Amount>10.00</Amount>
            </DepositInfo>
            <DepositInfo>
                <DepositType>SAV</DepositType>
                    <Amount>5.00</Amount>
            </DepositInfo>
</Deposit_Data>
<Deposit_Data>
    <MemberID>12345</MemberID>
    <MemberFirstName>Mickey</MemberFirstName>
    <MemberLastName>Mouse</MemberLastName>      
            <DepositDate>2023-12-05</DepositDate>
            <DepositInfo>
                <DepositType>CHK</DepositType>
                    <Amount>25.00</Amount>
            </DepositInfo>
            <DepositInfo>
                <DepositType>SAV</DepositType>
                    <Amount>10.00</Amount>
            </DepositInfo>
</Deposit_Data>
<Deposit_Data>
    <MemberID>78910</MemberID>
    <MemberFirstName>Donald</MemberFirstName>
    <MemberLastName>Duck</MemberLastName>      
            <DepositDate>2023-11-22</DepositDate>
            <DepositInfo>
                <DepositType>CHK</DepositType>
                    <Amount>10.00</Amount>
            </DepositInfo>
            <DepositInfo>
                <DepositType>SAV</DepositType>
                    <Amount>30.00</Amount>
            </DepositInfo>
</Deposit_Data>
<Deposit_Data>
    <MemberID>78910</MemberID>
    <MemberFirstName>Donald</MemberFirstName>
    <MemberLastName>Duck</MemberLastName>      
            <DepositDate>2023-12-05</DepositDate>
            <DepositInfo>
                <DepositType>CHK</DepositType>
                    <Amount>20.00</Amount>
            </DepositInfo>
            <DepositInfo>
                <DepositType>SAV</DepositType>
                    <Amount>10.00</Amount>
            </DepositInfo>
</Deposit_Data>
<Deposit_Data>
    <MemberID>78910</MemberID>
    <MemberFirstName>Donald</MemberFirstName>
    <MemberLastName>Duck</MemberLastName>      
            <DepositDate>2023-12-05</DepositDate>
            <DepositInfo>
                <DepositType>CHK</DepositType>
                    <Amount>5.00</Amount>
            </DepositInfo>
            <DepositInfo>
                <DepositType>SAV</DepositType>
                    <Amount>10.00</Amount>
            </DepositInfo>
</Deposit_Data>

The end result should be:

   MemberID|DepositDate|DepositType|Amount
   12345|2023-11-22|CHK|60
   12345|2023-11-22|SAV|35
   12345|2023-12-05|CHK|25
   12345|2023-12-05|SAV|10
   78910|2023-11-22|CHK|10
   78910|2023-11-22|SAV|30
   78910|2023-12-05|CHK|25
   78910|2023-12-05|SAV|20

I tried running with a for-each-group based on the date and deposit type which didn't work. Please see below for the XSLT.

<xsl:template match="Deposit_Data">

    <xsl:choose>
        
        <xsl:when test="exists(DepositInfo)">
            
            <xsl:for-each-group select="." group-by="DepositDate">
    
                <xsl:variable name="DepositDateOfCurrentGroup" select="current-grouping-key()"/>
                
                <!-- MemberID --><xsl:value-of select="MemberID"/>
                <xsl:value-of select="$vPipeDelimiter"/>
                <!-- DepositDate --><xsl:value-of select="format-date($DepositDateOfCurrentGroup,'[Y0001][M01][D01]')"/>
                <xsl:value-of select="$vPipeDelimiter"/>        
                
                
                <!-- DepositType -->                   
                <xsl:choose>
                    <xsl:when test="DepositInfo/DepositType = 'CHK'">
                        <!-- Amount --><xsl:text>ACCT1</xsl:text>
                    </xsl:when>
                    <xsl:when test="DepositInfo/DepositType = 'SAV'">
                        <!-- Amount --><xsl:text>ACCT2</xsl:text>
                    </xsl:when>
                    <xsl:when test="DepositInfo/DepositType = 'CD'">
                        <!-- Amount --><xsl:text>ACCT3</xsl:text>
                    </xsl:when>
                    <xsl:otherwise>
                        <xsl:text/>
                    </xsl:otherwise>
                </xsl:choose>
                <xsl:value-of select="$vPipeDelimiter"/>
                     
                
                
                <xsl:choose>
                  
                    <xsl:when test="exists(DepositDate = $DepositDateOfCurrentGroup and DepositInfo/DepositType = 'CHK')">
                        <xsl:value-of select="format-number(sum(DepositDate[DepositDate = $DepositDateOfCurrentGroup]/Amount),'#######0.00')"/>                                 
                    </xsl:when>
                    <xsl:when test="exists([DepositDate = $DepositDateOfCurrentGroup and DepositInfo/DepositType = 'SAV'])">
                        <xsl:value-of select="format-number(sum(DepositDate[DepositDate = $DepositDateOfCurrentGroup]/Amount),'#######0.00')"/>                                 
                    </xsl:when>
                    <xsl:when test="exists(DepositDate = $DepositDateOfCurrentGroup and DepositInfo/DepositType = 'CD')">
                        <xsl:value-of select="format-number(sum(DepositDate[DepositDate = $DepositDateOfCurrentGroup]/Amount),'#######0.00')"/>                                 
                    </xsl:when>
                    <xsl:otherwise>
                        <xsl:text>0.00</xsl:text>
                    </xsl:otherwise>
                </xsl:choose>   
               
                         
                                                  
                <xsl:value-of select="$vLineFeed"/>
                
              
    
            </xsl:for-each-group>
        
        </xsl:when>
        
        <xsl:otherwise>                   
              
            
        </xsl:otherwise>
        
    </xsl:choose>
    
</xsl:template>

The end result has no grouping:

MemberID|DepositDate|DepositType|Amount|
12345|20231122|CHK|0.00
12345|20231122|CHK|0.00
12345|20231205|CHK|0.00
78910|20231122|CHK|0.00
78910|20231205|CHK|0.00
78910|20231205|CHK|0.00

Revised XSLT

<xsl:stylesheet xmlns:xsl="http://www.w3.org/1999/XSL/Transform"
    version="3.0"
    xmlns:xs="http://www.w3.org/2001/XMLSchema"
    exclude-result-prefixes="#all">
    
    <xsl:output method="text" />
    <xsl:variable name="vPipeDelimiter" select="'|'" />
    <xsl:variable name="vLineFeed" select="'&#13;&#10;'" />    
    <xsl:variable name="FirstDepositDate">
        
        <xsl:choose>
            <xsl:when test="Report/Deposit_Data[DepositDate != ''][1]/DepositDate != ''">
                <xsl:value-of select="Report/Deposit_Data[DepositDate != '']/DepositDate"/>    
            </xsl:when>
            <xsl:otherwise>
                <xsl:value-of select="format-date(current-date(),'[Y0001]-[M01]-[D01]')"/>
            </xsl:otherwise>
        </xsl:choose>
    </xsl:variable>
    
    <xsl:template match="/">
        
        <xsl:call-template name="header"/>
        <xsl:call-template name="Deposit_Data" />
        
        
    </xsl:template>    
    
    <!--Header Template-->
    <xsl:template name="header">
        
        <!-- MemberID --><xsl:text>MemberID</xsl:text>
        <xsl:value-of select="$vPipeDelimiter"/>
        <!-- DepositDate--><xsl:text>DepositDate</xsl:text>
        <xsl:value-of select="$vPipeDelimiter"/>
        <!-- DepositType --><xsl:text>DepositType</xsl:text>
        <xsl:value-of select="$vPipeDelimiter"/>
        <!-- Amount --><xsl:text>Amount</xsl:text>
        <xsl:value-of select="$vPipeDelimiter"/>       
        
        <xsl:value-of select="$vLineFeed"/>            
        
    </xsl:template>
    
    
    <xsl:template name="Deposit_Data">
        <xsl:for-each-group select="Report/Deposit_Data" composite="yes" group-by="MemberID, DepositDate">
            <xsl:variable name="key" select="current-grouping-key()"/>
            <xsl:for-each-group select="current-group()/DepositInfo" group-by="DepositType">
                <!-- MemberID --><xsl:value-of select="../MemberID"/>
                <xsl:value-of select="$vPipeDelimiter"/>    
                <!-- MemberID --><xsl:value-of select="../DepositDate"/>
                <xsl:value-of select="$vPipeDelimiter"/>    
                
                
                <xsl:choose>
                    <xsl:when test="DepositType = 'CHK'">                       
                        <xsl:value-of select="'ACCT1',  sum(current-group()/Amount)" separator="|"/>
                    </xsl:when>
                    <xsl:when test="DepositType = 'SAV'">
                        <xsl:value-of select="'ACCT2',  sum(current-group()/Amount)" separator="|"/>
                    </xsl:when>
                    <xsl:when test="DepositInfo/DepositType = 'CD'">
                        <xsl:value-of select="'ACCT3',  sum(current-group()/Amount)" separator="|"/>
                    </xsl:when>
                    <xsl:otherwise>
                        <xsl:text/>
                    </xsl:otherwise>
                </xsl:choose>                
              
                <xsl:text>&#10;</xsl:text>        
            </xsl:for-each-group>
        </xsl:for-each-group>
    </xsl:template>
    
</xsl:stylesheet>

Please show us your for-each-group attempt and how it failed. — Martin Honnen
– Martin Honnen, Commented Dec 20, 2023 at 17:48

Martin Honnen · Accepted Answer · 2023-12-20 20:37:52Z

0

Using XSLT 3, you could use

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

  <xsl:output method="text" />

  <xsl:template match="/">
    <xsl:for-each-group select="Report/Deposit_Data" composite="yes" group-by="MemberID, DepositDate">
      <xsl:variable name="key" select="current-grouping-key()"/>
      <xsl:for-each-group select="current-group()/DepositInfo" group-by="DepositType">
        <xsl:value-of select="$key, current-grouping-key(), sum(current-group()/Amount)" separator="|"/>
        <xsl:text>&#10;</xsl:text>        
      </xsl:for-each-group>
    </xsl:for-each-group>
  </xsl:template>

</xsl:stylesheet>

Or

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

  <xsl:output method="text" />

  <xsl:template match="/">
    <xsl:for-each-group select="Report/Deposit_Data/DepositInfo" composite="yes" group-by="../MemberID, ../DepositDate, DepositType">
      <xsl:value-of select="current-grouping-key(), sum(current-group()/Amount)" separator="|"/>
      <xsl:text>&#10;</xsl:text>        
    </xsl:for-each-group>
  </xsl:template>

</xsl:stylesheet>

answered Dec 20, 2023 at 20:37

Martin Honnen

169k6 gold badges100 silver badges123 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Wasupbi Over a year ago

Thank you Martin! Let me try and add the logic to my existing code. I need to translate the deposit types to something else, for example CHK to be translated on the output to ACCT1. Appreciate your expertise.

Wasupbi Over a year ago

I've added a revised XSLT to the bottom of the post. Any issues with doing the deposit type translation that way? Thank you.

Martin Honnen Over a year ago

@Wasupbi, ask a new, separate question, it doesn't make sense to ask for grouping with code that doesn't group, then receive two grouping suggestions to then say you need some different, more complicated result.

Wasupbi Over a year ago

Will do, appreciate the help on this.

Collectives™ on Stack Overflow

XSLT Multiple Grouping

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related