Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations Westi on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Grouping and Summing

Status
Not open for further replies.
aswolff

aswolff

Programmer
Jul 31, 2006
100
US
Hello. I am trying to use the muenchian method to take an XML document shown below and consolidate it. The logic to use is:

If <LINE> and <ITEM> are the same in 2 or more <LINES> then SUM the <QTY> and delete all duplicate <LINES> nodes. My problem is how do I structure xslt so that the ROOT, HEADER, PO, VENDOR nodes are deep copied..while the LINES node and it's child nodes get consolidated and summed by QTY. See below XML samples:

Thank you very much for any insight into this problem

Code: 
<!--Take This Document..... -->
<ROOT>
 <HEADER>
   <PO>1</PO>
   <VENDOR>ABC</VENDOR>
   <LINES>
     <LINE>1</LINE>               <--Combine into 1 Node
     <ITEM>A</ITEM>
     <QTY>10</QTY>
   </LINES>
   <LINES>
     <LINE>2</LINE>
     <ITEM>B</ITEM>
     <QTY>15</QTY>
   </LINES>
   <LINES>
     <LINE>1</LINE>               <--Duplicate Line #1
     <ITEM>A</ITEM>
     <QTY>15</QTY>
   </LINES>
  </HEADER>
</ROOT>

<!--.....And turn it into this -->

<ROOT>
 <HEADER>
   <PO>1</PO>
   <VENDOR>ABC</VENDOR>
   <LINES>
     <LINE>1</LINE>
     <ITEM>A</ITEM>
     <QTY>25</QTY>
   </LINES>
   <LINES>
     <LINE>2</LINE>
     <ITEM>B</ITEM>
     <QTY>15</QTY>
   </LINES>
  </HEADER>
</ROOT>
Thanks.

 
Sort by date Sort by votes
This is what I have so far....it pretty lame:

Code: 
<?xml version="1.0" encoding="UTF-8"?>
<xsl:stylesheet xmlns:xsl="[URL unfurl="true"]http://www.w3.org/1999/XSL/Transform"[/URL] version="1.0">
    <xsl:output method="xml" indent="yes" xmlns:xalan="[URL unfurl="true"]http://xml.apache.org/xslt"[/URL]
        xalan:indent-amount="4" encoding="UTF-8"/>

    <xsl:key name="LINEKEY" match="LINES" use="concat(LINE,ITEM)"/>

    <xsl:template match="/ROOT/HEADER">
        <RESULTS>
            <xsl:apply-templates
                select="LINES[generate-id(.) = generate-id(key('LINEKEY',concat(LINE,ITEM)) [1])]"/>
        </RESULTS>
    </xsl:template>
    
    <xsl:template match="LINES">
        <xsl:for-each select=".">
            <xsl:copy-of select="."/>
        </xsl:for-each>
    </xsl:template>
    
</xsl:stylesheet>
 
Upvote 0 Downvote
Have you looked at my FAQ on this? faq426-6585

You are missing a couple key requirements to make the Meunchian Method work properly.

Tom Morrison
 
Upvote 0 Downvote
Thanks. Your sample code did shed some light. I am almost there. I am still not able to sum the quantities up.

The code:
Code: 
<?xml version="1.0" encoding="UTF-8"?>
<xsl:stylesheet xmlns:xsl="[URL unfurl="true"]http://www.w3.org/1999/XSL/Transform"[/URL] version="1.0">
    <xsl:output method="xml" indent="yes" xmlns:xalan="[URL unfurl="true"]http://xml.apache.org/xslt"[/URL]
        xalan:indent-amount="4" encoding="UTF-8"/>

    <xsl:key name="LINEKEY" match="LINES" use="concat(LINE,ITEM)"/>

    <xsl:template match="/">
        <ROOT>
            <HEADER>
                <xsl:for-each select="ROOT/HEADER/*">
                    <xsl:variable name="cur_tag" select="name(.)"/>
                    <xsl:choose>
                        <xsl:when test="$cur_tag !='LINES'">
                            <xsl:copy-of select="."/>
                        </xsl:when>
                    </xsl:choose>
                </xsl:for-each>

                <xsl:for-each
                    select="//LINES[generate-id(.) = generate-id(key('LINEKEY',concat(LINE,ITEM)) [1])]">
                    <xsl:variable name="cur_tag" select="name(.)"/>
                    <xsl:choose>
                        <xsl:when test="$cur_tag !='QTY'">
                            <xsl:copy-of select="."/>
                        </xsl:when>
                    </xsl:choose>
                </xsl:for-each>
            </HEADER>
        </ROOT>
    </xsl:template>
</xsl:stylesheet>

generates:
Code: 
<?xml version="1.0" encoding="UTF-8"?>
<ROOT>
 <HEADER>
  <PO>1</PO>
  <VENDOR>ABC</VENDOR>
  <LINES>
   <LINE>1</LINE>
   <ITEM>A</ITEM> 
   <QTY>10</QTY>  <!-- This needs to sum up to 25
  </LINES>
  <LINES>
   <LINE>2</LINE>
   <ITEM>B</ITEM>
   <QTY>15</QTY>     
  </LINES>
 </HEADER>
</ROOT>
 
Upvote 0 Downvote
You really are almost there!

If you are reasonably certain that tour QTY elements are always going to be valid, then you can use the sum() function, which takes a nodeset as its argument and returns the value of the sum. You can already express the nodeset. If you are unsure of the validity of the numeric data in the QTY elements, then you can process the nodeset in a recursive template that tests for numeric validity before using the QTY value in an arithmetic context.

IMO your use of copy-of is going to be a problem in your eventual solution. I think it would be better to use an explicit recursive template, the so-called identity template, to do the copying. An example, with tutorial, may be found here.

Tom Morrison
 
Upvote 0 Downvote
Okay, here is a solution. Some observations:[ul][li]I retargeted your use of xsl:copy-of[/li][li]I got rid of your predisposition toward procedural coding by using...[/li][li]node sets which is more 'natural' for a language such as XSLT[/li][li]this solution presumes that all QTY values are valid numbers (heed warning in previous post)[/li][/ul]

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

  <xsl:key name="LINEKEY" match="ROOT/HEADER/LINES" use="concat(LINE,ITEM)"/>

  <xsl:template match="/">
    <ROOT>
       <HEADER>
         [COLOR=blue]<!-- copy all the subordinate nodes except LINES -->[/color]
         <xsl:copy-of select="ROOT/HEADER/*[local-name()!='LINES']"/>
         [COLOR=blue]<!-- consolidate identical LINES -->[/color]
         <xsl:for-each select="//HEADER/LINES[generate-id(.) = generate-id(key('LINEKEY',concat(LINE,ITEM)) [1])]">
           <LINES>
             [COLOR=blue]<!-- copy all the subordinate nodes except QTY -->[/color]
             <xsl:copy-of select="*[local-name()!='QTY']"/>
             [COLOR=blue]<!-- sum all QTY for this combination of LINE and ITEM -->[/color]
             <QTY><xsl:value-of select="sum(key('LINEKEY', concat(LINE,ITEM))/QTY)"/></QTY>
           </LINES>
         </xsl:for-each>
       </HEADER>
    </ROOT>
  </xsl:template>
</xsl:stylesheet>

Code: 
<ROOT>
  <HEADER>
    <PO>1</PO>
    <VENDOR>ABC</VENDOR>
    <LINES>
      <LINE>1</LINE>
      <ITEM>A</ITEM>
      <QTY>25</QTY>
    </LINES>
    <LINES>
      <LINE>2</LINE>
      <ITEM>B</ITEM>
      <QTY>15</QTY>
    </LINES>
  </HEADER>
</ROOT>

Tom Morrison
 
Upvote 0 Downvote
OK..let me digest. I've been beating my head over this for the last 4-5 days. I was ready to give up. Thanks so much!
 
Upvote 0 Downvote
Ah, yes...[banghead]

You might want to reinsert your xalan specific stuff.
 
Upvote 0 Downvote
aswolff said:
OK..let me digest.
Click to expand...
Is it digested yet? In particular, have you determined that all your QTY fields contain valid numeric values? If not, there is still another step to do...

Tom Morrison
 
Upvote 0 Downvote
Yes. All QTY values are numeric fields. But it's possible one day they could send a null value.
 
Upvote 0 Downvote
aswolff said:
But it's possible one day they could send a null value.
Click to expand...

Let's simulate that possibility.
Code: 
<ROOT>
 <HEADER>
   <PO>1</PO>
   <VENDOR>ABC</VENDOR>
   <LINES>
     <LINE>1</LINE> 
     <ITEM>A</ITEM>
     <QTY>10</QTY>
   </LINES>
   <LINES>
     <LINE>1</LINE> 
     <ITEM>A</ITEM>
     <QTY>AARDVARK</QTY>
   </LINES>
   <LINES>
     <LINE>1</LINE> 
     <ITEM>A</ITEM>
     <QTY></QTY>
   </LINES>
   <LINES>
     <LINE>2</LINE>
     <ITEM>B</ITEM>
     <QTY>15</QTY>
   </LINES>
   <LINES>
     <LINE>1</LINE> 
     <ITEM>A</ITEM>
     <QTY>15</QTY>
   </LINES>
  </HEADER>
</ROOT>
Code: 
<xsl:stylesheet xmlns:xsl="[URL unfurl="true"]http://www.w3.org/1999/XSL/Transform"[/URL] version="1.0">
  <xsl:output method="xml" indent="yes" encoding="UTF-8"/>

  <xsl:key name="LINEKEY" match="ROOT/HEADER/LINES" use="concat(LINE,ITEM)"/>

  <xsl:template match="/">
    <ROOT>
       <HEADER>
         <xsl:copy-of select="ROOT/HEADER/*[local-name()!='LINES']"/>
         <xsl:for-each select="//HEADER/LINES[generate-id(.) = generate-id(key('LINEKEY',concat(LINE,ITEM)) [1])]">
		 <LINES>
		   <xsl:copy-of select="*[local-name()!='QTY']"/>
			<QTY>
		   <xsl:call-template name="SumNodes">
		   			<xsl:with-param name="nodeList" select="key('LINEKEY', concat(LINE,ITEM))/QTY"/>
		   </xsl:call-template>
			</QTY> 
		 </LINES>
         </xsl:for-each>
       </HEADER>
    </ROOT>
  </xsl:template>
  <xsl:template name="SumNodes">
	<xsl:param name="nodeList" />
	<xsl:choose>
	<xsl:when test="$nodeList">
		<xsl:variable name="myNode" select="$nodeList[1]"/>
		<xsl:variable name="myValue">
			<xsl:choose>
			<xsl:when test="number($myNode)">
				<xsl:value-of select="number($myNode)"/>
			</xsl:when>
			<xsl:otherwise>0</xsl:otherwise>
			</xsl:choose>
		</xsl:variable>
		<xsl:variable name="totalOfOthers">
			<xsl:call-template name="SumNodes">
				<xsl:with-param name="nodeList" select="$nodeList[position()!=1]"/>
			</xsl:call-template>
		</xsl:variable>
		<xsl:value-of select="$totalOfOthers + $myValue"/>
	</xsl:when>
	<xsl:otherwise>0</xsl:otherwise>		
	</xsl:choose>
  </xsl:template>
</xsl:stylesheet>

Tom Morrison
 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

198709km
  • Locked
  • Question
Grouping XML key value using XSLT
Replies
1
Views
741
atlopes
atlopes
Dashley
  • Locked
  • Question
Consuming a WSDL and passing arrays into an object
Replies
1
Views
684
Dashley
Dashley
xyninaxy
  • Locked
  • Question
Move layout from bottom to top (Android APK)(xml)
Replies
0
Views
689
xyninaxy
xyninaxy
StephenGunn
  • Locked
  • Question
xml from Team Center and conforming with XLST
Replies
1
Views
222
atlopes
atlopes
momo2000
  • Locked
  • Question
How do I find and display CaseParty address? 1
Replies
14
Views
246
k5tm
k5tm

Part and Inventory Search

Sponsor

Back
Top