Wrox Programmer Forums

Need to download code?

View our list of code downloads.

Go Back   Wrox Programmer Forums > XML > XSLT
Password Reminder
Register
Register | FAQ | Members List | Calendar | Search | Today's Posts | Mark Forums Read
XSLT General questions and answers about XSLT. For issues strictly specific to the book XSLT 1.1 Programmers Reference, please post to that forum instead.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the XSLT section of the Wrox Programmer to Programmer discussions. This is a community of tens of thousands of software programmers and website developers including Wrox book authors and readers. As a guest, you can read any forum posting. By joining today you can post your own programming questions, respond to other developersí questions, and eliminate the ads that are displayed to guests. Registration is fast, simple and absolutely free .
DRM-free e-books 300x50
Reply
 
Thread Tools Display Modes
  #1 (permalink)  
Old November 17th, 2017, 03:45 AM
Registered User
Points: 12, Level: 1
Points: 12, Level: 1 Points: 12, Level: 1 Points: 12, Level: 1
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Nov 2017
Posts: 3
Thanks: 1
Thanked 0 Times in 0 Posts
Default How fo find unique values belonging to a node block and sum values

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!!

Last edited by marthe; November 17th, 2017 at 03:58 AM. Reason: xslt version was missing
Reply With Quote
  #2 (permalink)  
Old November 17th, 2017, 03:52 AM
mhkay's Avatar
Wrox Author
Points: 18,392, Level: 59
Points: 18,392, Level: 59 Points: 18,392, Level: 59 Points: 18,392, Level: 59
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Apr 2004
Location: Reading, Berks, United Kingdom.
Posts: 4,947
Thanks: 0
Thanked 288 Times in 283 Posts
Default

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.
__________________
Michael Kay
http://www.saxonica.com/
Author, XSLT 2.0 and XPath 2.0 Programmer\'s Reference
Reply With Quote
  #3 (permalink)  
Old November 17th, 2017, 04:00 AM
Registered User
Points: 12, Level: 1
Points: 12, Level: 1 Points: 12, Level: 1 Points: 12, Level: 1
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Nov 2017
Posts: 3
Thanks: 1
Thanked 0 Times in 0 Posts
Default

Tnx, I put in the version, it is 1.0, and I have tried grouping, but couldn't get it to work...
Reply With Quote
  #4 (permalink)  
Old November 17th, 2017, 04:11 AM
mhkay's Avatar
Wrox Author
Points: 18,392, Level: 59
Points: 18,392, Level: 59 Points: 18,392, Level: 59 Points: 18,392, Level: 59
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Apr 2004
Location: Reading, Berks, United Kingdom.
Posts: 4,947
Thanks: 0
Thanked 288 Times in 283 Posts
Default

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.
__________________
Michael Kay
http://www.saxonica.com/
Author, XSLT 2.0 and XPath 2.0 Programmer\'s Reference
Reply With Quote
  #5 (permalink)  
Old November 17th, 2017, 07:12 AM
Registered User
Points: 12, Level: 1
Points: 12, Level: 1 Points: 12, Level: 1 Points: 12, Level: 1
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Nov 2017
Posts: 3
Thanks: 1
Thanked 0 Times in 0 Posts
Default

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??
Reply With Quote
  #6 (permalink)  
Old November 17th, 2017, 07:15 AM
Friend of Wrox
Points: 6,612, Level: 34
Points: 6,612, Level: 34 Points: 6,612, Level: 34 Points: 6,612, Level: 34
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Nov 2007
Location: Germany
Posts: 1,234
Thanks: 0
Thanked 242 Times in 241 Posts
Default

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.
__________________
Martin Honnen
Microsoft MVP (XML, Data Platform Development) 2005/04 - 2013/03
My blog
Reply With Quote
The Following User Says Thank You to Martin Honnen For This Useful Post:
marthe (November 17th, 2017)
Reply


Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are Off
Pingbacks are On
Refbacks are Off

Similar Threads
Thread Thread Starter Forum Replies Last Post
unique values Vision G Access VBA 1 December 19th, 2011 08:55 AM
Getting unique values from substring rabs XSLT 6 March 13th, 2009 03:10 PM
Getting unique values from substring rabs XSLT 16 March 5th, 2009 12:39 PM
how can find Sum of all book values where BookType Rg2005 SQL Server 2000 2 October 8th, 2007 03:03 AM
Unique values from a dataset gbuller Pro VB.NET 2002/2003 3 September 27th, 2004 12:17 PM



All times are GMT -4. The time now is 11:00 AM.


Powered by vBulletin®
Copyright ©2000 - 2018, Jelsoft Enterprises Ltd.
© 2013 John Wiley & Sons, Inc.