CODEWITHSUNDEEP
xmlxslt
user2992769
user2992769

Reputation: 33

Group and Sum node values in XSLT 1.0

I would like to group the nodes and Sum based on a node in the XML. However my XSLT code is grouping and summing all the Cat_Code s irrespective of ITEM. Expected O/P should be grouping all the Sub_Category nodes under an ITEM and Sum the AMOUNT for same Cat_Code.

Below id the XML source:

<?xml version="1.0" encoding="UTF-8"?>
<Data>
   <Entry>
      <ITEM Id="1111">         
         <Category>Type CAT</Category>         
         <Manufacture_Date>2014-01-12</Manufacture_Date>         
         <Sub_Category>
            <Cat_Code>01</Cat_Code>
            <Expiry_Dt>2015-12-15</Expiry_Dt>            
            <Cat_Type>04W</Cat_Type>
            <AMOUNT>100</AMOUNT>
         </Sub_Category>
         <Sub_Category>
            <Cat_Code>02</Cat_Code>
            <Expiry_Dt>2015-12-15</Expiry_Dt>            
            <Cat_Type>PENDCCER</Cat_Type>
            <AMOUNT>50</AMOUNT>
         </Sub_Category>
         <Sub_Category>
            <Cat_Code>03</Cat_Code>
            <Expiry_Dt>2015-12-15</Expiry_Dt>
            <Cat_Type>PENDCOER</Cat_Type>
            <AMOUNT>50</AMOUNT>
         </Sub_Category>
         <Sub_Category>
            <Cat_Code>04</Cat_Code>
            <Expiry_Dt>2015-12-15</Expiry_Dt>
            <Cat_Type>PENDCO</Cat_Type>
            <AMOUNT>150</AMOUNT>
         </Sub_Category> 
         <Sub_Category>
            <Cat_Code>03</Cat_Code>
            <Expiry_Dt>2014-08-15</Expiry_Dt>
            <Cat_Type>PENDCOER</Cat_Type>
            <AMOUNT>75</AMOUNT>
         </Sub_Category>
         <Sub_Category>
            <Cat_Code>04</Cat_Code>
            <Expiry_Dt>2014-08-15</Expiry_Dt>
            <Cat_Type>PENDCO</Cat_Type>
            <AMOUNT>75</AMOUNT>
         </Sub_Category>         
      </ITEM>
      <ITEM Id="1112">
         <Category>Type CAT</Category>         
         <Manufacture_Date>2014-01-01</Manufacture_Date>   
          <Sub_Category>
            <Cat_Code>01</Cat_Code>
            <Expiry_Dt>2015-12-15</Expiry_Dt>            
            <Cat_Type>04W</Cat_Type>
            <AMOUNT>100</AMOUNT>
         </Sub_Category>
         <Sub_Category>
            <Cat_Code>02</Cat_Code>
            <Expiry_Dt>2015-12-15</Expiry_Dt>            
            <Cat_Type>PENDCCER</Cat_Type>
            <AMOUNT>50</AMOUNT>
         </Sub_Category>
         <Sub_Category>
            <Cat_Code>05</Cat_Code>
            <Expiry_Dt>2015-12-15</Expiry_Dt>
            <Cat_Type>XPED</Cat_Type>
            <AMOUNT>300</AMOUNT>
         </Sub_Category>
         <Sub_Category>
            <Cat_Code>05</Cat_Code>
            <Expiry_Dt>2014-08-15</Expiry_Dt>
            <Cat_Type>XPED</Cat_Type>
            <AMOUNT>250</AMOUNT>
         </Sub_Category> 
         <Sub_Category>
            <Cat_Code>03</Cat_Code>
            <Expiry_Dt>2014-08-15</Expiry_Dt>
            <Cat_Type>PENDCOER</Cat_Type>
            <AMOUNT>50</AMOUNT>
         </Sub_Category>
         <Sub_Category>
            <Cat_Code>04</Cat_Code>
            <Expiry_Dt>2014-08-15</Expiry_Dt>
            <Cat_Type>PENDCO</Cat_Type>
            <AMOUNT>100</AMOUNT>
         </Sub_Category>  
      </ITEM>
   </Entry>
</Data>

Expected Output File

    <?xml version="1.0" encoding="UTF-8"?>
<Data>
   <Entry>
      <ITEM Id="1111">         
         <Category>Type CAT</Category>         
         <Manufacture_Date>2014-01-12</Manufacture_Date>         
         <Sub_Category>
            <Cat_Code>01</Cat_Code>            
            <Cat_Type>04W</Cat_Type>
            <AMOUNT>100</AMOUNT>
         </Sub_Category>
         <Sub_Category>
            <Cat_Code>02</Cat_Code>            
            <Cat_Type>PENDCCER</Cat_Type>
            <AMOUNT>50</AMOUNT>
         </Sub_Category>
         <Sub_Category>
            <Cat_Code>03</Cat_Code>            
            <Cat_Type>PENDCOER</Cat_Type>
            <AMOUNT>125</AMOUNT>
         </Sub_Category>
         <Sub_Category>
            <Cat_Code>04</Cat_Code>            
            <Cat_Type>PENDCO</Cat_Type>
            <AMOUNT>225</AMOUNT>
         </Sub_Category>         
      </ITEM>
      <ITEM Id="1112">
         <Category>Type CAT</Category>         
         <Manufacture_Date>2014-01-01</Manufacture_Date>   
          <Sub_Category>
            <Cat_Code>01</Cat_Code>            
            <Cat_Type>04W</Cat_Type>
            <AMOUNT>100</AMOUNT>
         </Sub_Category>
         <Sub_Category>
            <Cat_Code>02</Cat_Code>
            <Cat_Type>PENDCCER</Cat_Type>
            <AMOUNT>50</AMOUNT>
         </Sub_Category>
         <Sub_Category>
            <Cat_Code>05</Cat_Code>Expiry_Dt>
            <Cat_Type>XPED</Cat_Type>
            <AMOUNT>550</AMOUNT>
         </Sub_Category>
         <Sub_Category>
            <Cat_Code>03</Cat_Code>
            <Cat_Type>PENDCOER</Cat_Type>
            <AMOUNT>50</AMOUNT>
         </Sub_Category>
         <Sub_Category>
            <Cat_Code>04</Cat_Code>Expiry_Dt>
            <Cat_Type>PENDCO</Cat_Type>
            <AMOUNT>100</AMOUNT>
         </Sub_Category>  
      </ITEM>
   </Entry>
</Data>

Here is my XSLT Code

    <xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform">
    <xsl:output indent="yes"/>
    <xsl:strip-space elements="*"/>

    <xsl:key name="groupkey" match="Sub_Category" use="concat(../ITEM/Sub_Category, '+', Cat_Type)"/>

    <xsl:template match="@*|node()">
        <xsl:copy>
            <xsl:apply-templates select="@*|node()"/>
        </xsl:copy>
    </xsl:template>

    <xsl:template match="Data/Entry">
        <xsl:for-each select="ITEM">
                    <ITEM>
                        <xsl:attribute name="Id">
                            <xsl:value-of select="@Id" />
                        </xsl:attribute>
            <xsl:for-each select="Sub_Category[generate-id()=generate-id(key('groupkey',concat(../ITEM/Sub_Category, '+', Cat_Type))[1])]">
                <xsl:copy>                        

                                <Cat_Code><xsl:value-of select="Cat_Code"/></Cat_Code>
                                <Cat_Type><xsl:value-of select="Cat_Type"/></Cat_Type>                                  
                                <AMOUNT><xsl:value-of select="sum(key('groupkey',concat(../ITEM/Sub_Category, '+', Cat_Type))/AMOUNT)"/></AMOUNT>

                </xsl:copy>
            </xsl:for-each>
        </ITEM>
        </xsl:for-each>
    </xsl:template>

</xsl:stylesheet>

Upvotes: 0

Views: 1617

Answers (1)

michael.hor257k
michael.hor257k

Reputation: 116959

grouping all the Sub_Category nodes under an ITEM and Sum the AMOUNT for same Cat_Code."

Then why is your key concatenating Sub_Category and Cat_Type? That just doesn't make sense. I also don't see why you need the identity transform template in there. It seems like you are using it as some sort of incantation for good luck.

Try it this way instead:

<xsl:stylesheet version="1.0" 
xmlns:xsl="http://www.w3.org/1999/XSL/Transform">
<xsl:output method="xml" version="1.0" encoding="UTF-8" indent="yes"/>

<xsl:key name="groupkey" match="Sub_Category" use="concat(../@Id, '+', Cat_Code)"/>

<xsl:template match="/">
    <xsl:for-each select="Data/Entry/ITEM">
        <xsl:copy> 
        <xsl:copy-of select="@*"/>
        <xsl:for-each select="Sub_Category[generate-id()=generate-id(key('groupkey', concat(../@Id, '+', Cat_Code))[1])]">
            <xsl:copy>                        
                <xsl:copy-of select="Cat_Code"/>
                <xsl:copy-of select="Cat_Type"/>                                
                <AMOUNT><xsl:value-of select="sum(key('groupkey', concat(../@Id, '+', Cat_Code))/AMOUNT)"/></AMOUNT>
            </xsl:copy>
        </xsl:for-each>
    </xsl:copy> 
    </xsl:for-each>
</xsl:template>

</xsl:stylesheet>

Upvotes: 1

Related Questions