xsl-list
[Top] [All Lists]

[xsl] [XSL] Grouping and Sum problem - XSL 1.0

2008-04-29 06:23:24
Hello,

This is my first post here and I am quite new to XSL.  Im having
problems getting the sum for the royalty, mechanicals, taxes and
expenses elements of my grouping to sum correctly.  Im trying to
get it to sum the amounts for each row[type='RELEASE'] by the
current following::sibling order and releaseid elements.  I can
only total the amounts for the first immediately following
sibling row...  Any help with this is extremely appreciated as
this has been eluding me for the last 2 weeks...

The input:
 
<?xml version="1.0" encoding="UTF-8"?>
<document xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance";>
        <row>
                <id>25271</id>
                <order>7903</order>
                <userid>1588</userid>
                <type>TRACK</type>
                <trackid>48430</trackid>
                <artist>Aqau</artist>
                <releaseid>12302</releaseid>
                <releasename>Nohwai</releasename>
                <catalognumber>SLRS016</catalognumber>
                <upc>5060131014227</upc>
                <labelid>10941</labelid>
                <labelname>Solar</labelname>
                <price>220</price>
                <fee>100</fee>
                <amount>320</amount>
                <royalty>175.76</royalty>
                <mechanicals>0</mechanicals>
                <taxes>16</taxes>
                <expense>49.6</expense>
                <year>2008</year>
        </row>
        <row>
                <id>25324</id>
                <order>7927</order>
                <userid>1324</userid>
                <type>RELEASE</type>
                <trackid>null</trackid>
                <labelid>11184</labelid>
                <labelname>TRAUP</labelname>
                <distributorid>1038</distributorid>
                <price>440</price>
                <fee>200</fee>
                <amount>640</amount>
                <royalty>351.52</royalty>
                <mechanicals>0</mechanicals>
                <taxes>32</taxes>
                <expense>99.2</expense>
                <year>2008</year>
        </row>
        <row>
                <id>25325</id>
                <order>7927</order>
                <userid>1324</userid>
                <type>TRACK</type>
                <trackid>49607</trackid>
                <artist>Domini</artist>
                <releaseid>12560</releaseid>
                <releasename>Herb</releasename>
                <catalognumber>TRV87</catalognumber>
                <upc>880319295614</upc>
                <labelid>11184</labelid>
                <labelname>TRAUP</labelname>
                <price>0</price>
                <fee>0</fee>
                <amount>0</amount>
                <royalty>-11.011</royalty>
                <mechanicals>16.94</mechanicals>
                <taxes>0</taxes>
                <expense>16.94</expense>
                <year>2008</year>
        </row>
        <row>
                <id>25326</id>
                <order>7927</order>
                <userid>1324</userid>
                <type>TRACK</type>
                <trackid>49608</trackid>
                <artist>Domini</artist>
                <releaseid>12560</releaseid>
                <releasename>Herb</releasename>
                <catalognumber>TRV87</catalognumber>
                <upc>880319295614</upc>
                <labelid>11184</labelid>
                <labelname>TRAUP</labelname>
                <price>0</price>
                <fee>0</fee>
                <amount>0</amount>
                <royalty>-11.011</royalty>
                <mechanicals>16.94</mechanicals>
                <taxes>0</taxes>
                <expense>16.94</expense>
                <year>2008</year>
        </row>
        <row>
                <id>25271</id>
                <order>7927</order>
                <userid>1588</userid>
                <type>TRACK</type>
                <trackid>48437</trackid>
                <artist>Myst</artist>
                <releaseid>12367</releaseid>
                <releasename>Slip</releasename>
                <catalognumber>SLP01</catalognumber>
                <upc>7060131016229</upc>
                <labelid>11184</labelid>
                <labelname>TRAUP</labelname>
                <price>220</price>
                <fee>0</fee>
                <amount>220</amount>
                <royalty>109.75</royalty>
                <mechanicals>0</mechanicals>
                <taxes>11</taxes>
                <expense>39.5</expense>
                <year>2008</year>
        </row>
</document>


The current XLST Conversion:

<?xml version='1.0' encoding='utf-8' ?>
<xsl:stylesheet version="1.0"
xmlns:xsl="http://www.w3.org/1999/XSL/Transform";>
        <xsl:key name="RowByType" match="row"
use="concat(generate-id(preceding-sibling::row[1][type='RELEASE']
), releaseid)"/>
        <xsl:template match="/*">
                <xsl:copy>
                        <xsl:for-each select="row[type !=
'RELEASE'][count(.|key('RowByType',
concat(generate-id(preceding-sibling::row[type='RELEASE'][1]),
releaseid))[1] )= 1]">
                                <xsl:copy>
                                        <xsl:copy-of
select="id"/>
                                        <xsl:copy-of
select="site"/>
                                        <xsl:copy-of
select="order"/>
                                        <xsl:copy-of
select="date"/>
                                        <xsl:copy-of
select="userid"/>
                                        <type>
                                                <xsl:choose>
                                                        <xsl:when
test="preceding-sibling::row[1][type='RELEASE']">
        
<xsl:value-of select="'RELEASE'"/>
        
</xsl:when>
        
<xsl:otherwise>
        
<xsl:value-of select="'TRACK'"/>
        
</xsl:otherwise>
                                                </xsl:choose>
                                        </type>
                                        <xsl:copy-of
select="trackid"/>
                                        <xsl:copy-of
select="title"/>
                                        <xsl:copy-of
select="mixtitle"/>
                                        <xsl:copy-of
select="artist"/>
                                        <xsl:copy-of
select="remixer"/>
                                        <xsl:copy-of
select="releaseid"/>
                                        <xsl:copy-of
select="releasename"/>
                                        <xsl:copy-of
select="catalognumber"/>
                                        <xsl:copy-of
select="upc"/>
                                        <xsl:copy-of
select="genre"/>
                                        <xsl:copy-of
select="copyright"/>
                                        <xsl:copy-of
select="composer"/>
                                        <xsl:copy-of
select="lyricyst"/>
                                        <xsl:copy-of
select="medley"/>
                                        <xsl:copy-of
select="isrc"/>
                                        <xsl:copy-of
select="chartid"/>
                                        <xsl:copy-of
select="charttitle"/>
                                        <xsl:copy-of
select="labelid"/>
                                        <xsl:copy-of
select="labelname"/>
                                        <xsl:copy-of
select="distributorid"/>
                                        <xsl:copy-of
select="distributorname"/>
                                        <xsl:variable
name="AddSum">
                                                <xsl:value-of
select="preceding-sibling::row[type='RELEASE'][1]/following-sibli
ng::row[1]/releaseid"/>
                                        </xsl:variable>
                                                <price>
        
<xsl:choose>
        
<xsl:when test="releaseid = $AddSum">
        
<xsl:value-of select="sum(key('RowByType',
concat(generate-id(preceding-sibling::row[type='RELEASE'][1]),
releaseid))/price) +
preceding-sibling::row[type='RELEASE'][1]/price"/>
        
</xsl:when>
        
<xsl:otherwise>
        
<xsl:value-of select="price"/>
        
</xsl:otherwise>
        
</xsl:choose>
                                                </price>
                                                <fee>
        
<xsl:choose>
        
<xsl:when test="releaseid = $AddSum">
        
<xsl:value-of select="sum(key('RowByType',
concat(generate-id(preceding-sibling::row[type='RELEASE'][1]),
releaseid))/fee) +
preceding-sibling::row[type='RELEASE'][1]/fee"/>
        
</xsl:when>
        
<xsl:otherwise>
        
<xsl:value-of select="fee"/>
        
</xsl:otherwise>
        
</xsl:choose>
                                                </fee>
                                                <amount>
        
<xsl:choose>
        
<xsl:when test="releaseid = $AddSum">
        
<xsl:value-of select="sum(key('RowByType',
concat(generate-id(preceding-sibling::row[type='RELEASE'][1]),
releaseid))/amount) +
preceding-sibling::row[type='RELEASE'][1]/amount"/>
        
</xsl:when>
        
<xsl:otherwise>
        
<xsl:value-of select="amount"/>
        
</xsl:otherwise>
        
</xsl:choose>
                                                </amount>
                                                <royalty>
        
<xsl:choose>
        
<xsl:when test="releaseid = $AddSum">
        
<xsl:value-of select="sum(key('RowByType',
concat(generate-id(preceding-sibling::row[type='RELEASE'][1]),
releaseid))/royalty) +
preceding-sibling::row[type='RELEASE'][1]/royalty"/>
        
</xsl:when>
        
<xsl:otherwise>
        
<xsl:value-of select="royalty"/>
        
</xsl:otherwise>
        
</xsl:choose>
                                                </royalty>
                                                <mechanicals>
        
<xsl:choose>
        
<xsl:when test="releaseid = $AddSum">
        
<xsl:value-of select="sum(key('RowByType',
concat(generate-id(preceding-sibling::row[type='RELEASE'][1]),
releaseid))/mechanicals) +
preceding-sibling::row[type='RELEASE'][1]/mechanicals"/>
        
</xsl:when>
        
<xsl:otherwise>
        
<xsl:value-of select="mechanicals"/>
        
</xsl:otherwise>
        
</xsl:choose>
                                                </mechanicals>
                                                <taxes>
        
<xsl:choose>
        
<xsl:when test="releaseid = $AddSum">
        
<xsl:value-of select="sum(key('RowByType',
concat(generate-id(preceding-sibling::row[type='RELEASE'][1]),
releaseid))/taxes) +
preceding-sibling::row[type='RELEASE'][1]/taxes"/>
        
</xsl:when>
        
<xsl:otherwise>
        
<xsl:value-of select="taxes"/>
        
</xsl:otherwise>
        
</xsl:choose>
                                                </taxes>
                                                <expense>
        
<xsl:choose>
        
<xsl:when test="releaseid = $AddSum">
        
<xsl:value-of select="sum(key('RowByType',
concat(generate-id(preceding-sibling::row[type='RELEASE'][1]),
releaseid))/expense) +
preceding-sibling::row[type='RELEASE'][1]/expense"/>
        
</xsl:when>
        
<xsl:otherwise>
        
<xsl:value-of select="expense"/>
        
</xsl:otherwise>
        
</xsl:choose>
                                                </expense>
                                        
                                        <xsl:copy-of
select="year"/>
                                        <xsl:copy-of
select="term"/>
                                        <xsl:copy-of
select="state"/>
                                </xsl:copy>
                        </xsl:for-each>
                </xsl:copy>
        </xsl:template>
</xsl:stylesheet>


And finally my desired output:

<?xml version="1.0"?>
<document xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance";>
<row>
<id>25271</id>
<order>7903</order>
<userid>1588</userid>
<type>TRACK</type>
<trackid>48430</trackid>
<artist>Aqau</artist>
<releaseid>12302</releaseid>
<releasename>Nohwai</releasename>
<catalognumber>SLRS016</catalognumber>
<upc>5060131014227</upc>
<labelid>10941</labelid>
<labelname>Solar</labelname>
<price>220</price>
<fee>100</fee>
<amount>320</amount>
<royalty>175.76</royalty>
<mechanicals>0</mechanicals>
<taxes>16</taxes>
<expense>49.6</expense>
<year>2008</year>
</row>
<row>
<id>25325</id>
<order>7927</order>
<userid>1324</userid>
<type>RELEASE</type>
<trackid>49607</trackid>
<artist>Domini</artist>
<releaseid>12560</releaseid>
<releasename>Herb</releasename>
<catalognumber>TRV87</catalognumber>
<upc>880319295614</upc>
<labelid>11184</labelid>
<labelname>TRAUP</labelname>
<price>440</price>
<fee>200</fee>
<amount>640</amount>
<royalty>329.498</royalty>
<mechanicals>33.88</mechanicals>
<taxes>32</taxes>
<expense>133.08</expense>
<year>2008</year>
</row>
<row>
<id>25271</id>
<order>7927</order>
<userid>1588</userid>
<type>TRACK</type>
<trackid>48437</trackid>
<artist>Myst</artist>
<releaseid>12367</releaseid>
<releasename>Slip</releasename>
<catalognumber>SLP01</catalognumber>
<upc>7060131016229</upc>
<labelid>11184</labelid>
<labelname>TRAUP</labelname>
<price>220</price>
<fee>0</fee>
<amount>220</amount>
<royalty>109.75</royalty>
<mechanicals>0</mechanicals>
<taxes>11</taxes>
<expense>39.5</expense>
<year>2008</year>
</row>
</document>



--~------------------------------------------------------------------
XSL-List info and archive:  http://www.mulberrytech.com/xsl/xsl-list
To unsubscribe, go to: http://lists.mulberrytech.com/xsl-list/
or e-mail: <mailto:xsl-list-unsubscribe(_at_)lists(_dot_)mulberrytech(_dot_)com>
--~--

<Prev in Thread] Current Thread [Next in Thread>