How fo find unique values belonging to a node block and sum values

marthe · November 17th, 2017, 04:45 AM

I have a problem with calculation in XSLT, version 1.0. I have a xml with several Item-node blocks, se excample xml. Each item block contains a ProductId node, a specific ProductId may appear in one or several item blocks. What I want to do with XSLT is to get just one item block pr ProductId, I have managed to do that, see provided xslt. So all OK so far, my remaining problem now is to sum the quantity for each item block that belongs to the ProductId and print that quantity in the item block.
XML (simplified):

Code:

 
<?xml version="1.0" encoding="utf-8"?>
<ContainerComplete xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
  <TransactionId>123</TransactionId>
    <CustomerId>000123</CustomerId>
  <Container>
    <Ssccid>11776</Ssccid>
       <Items>
   <Item>
         <ProductId>704</ProductId>
        <ProductName>Sykkel</ProductName>
        <Quantity>13.000</Quantity>
         <LocationId>Loc_5</LocationId>        
      </Item>
      <Item>
        <ProductId>703</ProductId>
        <ProductName>Car</ProductName>
        <Quantity>2.000</Quantity>
        <LocationId>Loc_1</LocationId>        
      </Item>
      <Item>
        <ProductId>702</ProductId>
        <ProductName>MC</ProductName>
        <Quantity>40.000</Quantity>
        <LocationId>Loc_2</LocationId>        
      </Item>
      <Item>
        <ProductId>702</ProductId>
        <ProductName>MC</ProductName>
        <Quantity>90.000</Quantity>
        <LocationId>Loc_4</LocationId>
      </Item>
      <Item>
        <ProductId>703</ProductId>
        <ProductName>Car</ProductName>
        <Quantity>1.000</Quantity>
        <LocationId>Loc_3</LocationId>
       <Item>
        <ProductId>702</ProductId>
        <ProductName>MC</ProductName>
        <Quantity>2.000</Quantity>
        <LocationId>Loc_11</LocationId>
      </Item>
      </Item>
    </Items>
  </Container>
</ContainerComplete>

XSLT as of now:

Code:

 
<xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform">
    <xsl:output method="xml" indent="yes" omit-xml-declaration="yes"/> 
         <xsl:template match="/"> 
              <TransactionId> 
                  <xsl:value-of select="/ContainerComplete/TransactionId"/>
              </TransactionId>
               <Container>
                       <Sccid>
                            <xsl:value-of select="/Ssccid"/>
                        </Sccid>
                         <Items>
                             <xsl:for-each select="/ContainerComplete/Container/Items/Item/ProductId[not(.=preceding::*)]">
                                  <Item>
                                      <ProductId>
                                           <xsl:value-of select="."/>
                                      </ProductId>
                                      <ProductName>
                                           <xsl:value-of select="../ProductName"/>
                                       </ProductName>
                                       <Quantity>
                                            <xsl:value-of select="../Quantity"/>
<!-- Here is where I want the sum of all Quantity nodes in Item blocks containing this ProductId, and not just the Quantity for one ProductId -->
                                        </Quantity>
                                         <LocationId>
                                              <xsl:value-of select="../LocationId"/>
                                          </LocationId>    
                                      </Item>
                                </xsl:for-each> 
                             </Items>
                     </Container>
            </xsl:template> 
</xsl:stylesheet>

Resulting xml as of now:

Code:

  <TransactionId>123</TransactionId>
<Container>
    <Sccid/>
    <Items>
        <Item>
	<Item>
	   <ProductId>704</ProductId>
	   <ProductName>Sykkel</ProductName>
	   <Quantity>13.000</Quantity>
	    <LocationId>Loc_5</LocationId>        
         </Item>
            <ProductId>703</ProductId>
            <ProductName>Car</ProductName>
            <Quantity>2.000</Quantity>              
            <LocationId>Loc_1</LocationId>
        </Item>
        <Item>
            <ProductId>702</ProductId>
            <ProductName>MC</ProductName>
            <Quantity>40.000</Quantity>
             <LocationId>Loc_2</LocationId>
        </Item>
    </Items>
</Container>

I have struggled with this problem for a long time so please help!!

mhkay · November 17th, 2017, 04:52 AM

You don't say which version of XSLT you are using.

This is a standard grouping problem. As such, it is much easier to solve in XSLT 2.0 using the xsl:for-each-group instruction. (It's very similar to the first example in the spec at https://www.w3.org/TR/xslt20/#grouping-examples).

If for some reason you are stuck with XSLT 1.0, then it's rather harder. Look up "Muenchian Grouping" in your favourite XSLT text book or online tutorial and you will probably find an example that does something similar.

Either way, once you know that the keyword is "XSLT Grouping" you'll find it much easier to find a solution.

marthe · November 17th, 2017, 05:00 AM

Tnx, I put in the version, it is 1.0, and I have tried grouping, but couldn't get it to work...

mhkay · November 17th, 2017, 05:11 AM

Try posting your code. You're much more likely to find someone prepared to tell you where you code is wrong, than to write the code for you from scratch. Personally, I'm afraid I haven't coded in XSLT 1.0 for over 10 years so it's all very rusty. Upgrade if you possibly can.

marthe · November 17th, 2017, 08:12 AM

I tried the muenchian method, but I still can't sum the quantity nodes...

My XSLT is now like this:

Code:

 
<xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform">
   <xsl:output method="xml" indent="yes" omit-xml-declaration="yes"/>
      <xsl:key name="unike-prod-ids" match="Item" use="ProductId" />
         <xsl:template match="/">
         <TransactionId>
	     <xsl:value-of select="ContainerComplete/TransactionId"/>
	 </TransactionId>
	  <Container>
	     <Ssccid>
		<xsl:value-of select="ContainerComplete/Container/Ssccid"/>
	     </Ssccid>
	     <xsl:apply-templates select = "/ContainerComplete/Container/Items"/>				
	</Container>
      </xsl:template>  
      <xsl:template match="Items">
        <Items>
	<!--Only find the 'first' instance of each Item-->   
	<xsl:for-each select="Item[count(. | key('unike-prod-ids', ProductId)[1]) = 1]">
	    <xsl:sort select="ProductId" />
	    <ProductId>
	       <xsl:value-of select="ProductId" />
	    </ProductId>
	   <Quantity>
		<xsl:for-each select="key('unike-prod-ids', ProductId)">
		      <xsl:sort select="Quantity" />
		      <xsl:value-of select="Quantity" /> 		
		      <xsl:text>, </xsl:text>
		 </xsl:for-each>
	   </Quantity>
           </xsl:for-each>
       </Items>
     </xsl:template> 
</xsl:stylesheet>

<!-- Resulting xml: 
    <TransactionId>123</TransactionId>
       <Container>
        <Ssccid>11776</Ssccid>
        <ContainerVolume/>
          <Items>
            <ProductId>702</ProductId>
            <Quantity>2.000, 40.000, 90.000, </Quantity>
            <ProductId>703</ProductId>
            <Quantity>1.000, 2.000, </Quantity>
            <ProductId>704</ProductId>
            <Quantity>13.000, </Quantity>
        </Items>
    </Container>
-->

I tried to change the for-each block where I now write the quantity to this:

Code:

<Quantity>
    <xsl:for-each select="key('unike-prod-ids', ProductId)">
	<xsl:value-of select="sum(Quantity)"/>
    </xsl:for-each>
</Quantity>

But this did not give the correct sum. The result was like this:
<Item>
<ProductId>702</ProductId>
<Quantity>40902</Quantity>
<ProductId>703</ProductId>
<Quantity>21</Quantity>
<ProductId>704</ProductId>
<Quantity>13</Quantity>
</Item>

The correct sums should've been:
For ProductId 702: 132
For ProductId 703: 3
For ProductId 704: 13

So can anybody help with how to sum this up correctly??

Martin Honnen · November 17th, 2017, 08:15 AM

Use

Code:

<xsl:value-of select="sum(key('unike-prod-ids', ProductId)/Quantity)"/>

to sum the Quantity of items you have grouped with MUenchian grouping.