RE: Grouping, Sorting on Aggegrated sum of the groupin

Play the video

Subject: RE: Grouping, Sorting on Aggegrated sum of the groupings, for Top XX Group
From: "Aron Bock" <aronbock@xxxxxxxxxxx>
Date: Tue, 14 Jun 2005 14:55:12 +0000

Darin,

Here's an approach using the extension function "nodeset". MSXML has it as "node-set". This can be done without nodeset, but AFAICT those approaches are longer. Thus, on your input (below), this XSL:

<?xml version="1.0" encoding="iso8859-1"?> <xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform" xmlns:xalan="http://xml.apache.org/xalan" exclude-result-prefixes="xalan">

<xsl:output method="xml" indent="yes"/>

<xsl:template match="x"> <xsl:variable name="sorted-y"> <xsl:for-each select="y"> <xsl:sort select="@Value" order="descending" data-type="number"/> <xsl:copy-of select="."/> </xsl:for-each> </xsl:variable>

<xsl:copy> <xsl:apply-templates select="xalan:nodeset($sorted-y)/y[position() <= 5]"/> <xsl:call-template name="sum-y"> <xsl:with-param name="n" select="xalan:nodeset($sorted-y)/y[position() > 5]"/> </xsl:call-template> </xsl:copy> </xsl:template>

   <xsl:template match="y">
       <xsl:copy-of select="."/>
   </xsl:template>

   <xsl:template name="sum-y">
       <xsl:param name="n" select="/.."/>
       <xsl:param name="total" select="0"/>

<xsl:choose> <xsl:when test="count($n) > 0"> <xsl:call-template name="sum-y"> <xsl:with-param name="n" select="$n[position() != 1]"/> <xsl:with-param name="total" select="$total + $n[1]/@Value"/> </xsl:call-template> </xsl:when> <xsl:otherwise> <remainder Value="{$total}"/> </xsl:otherwise> </xsl:choose> </xsl:template>

</xsl:stylesheet>

produces:

<?xml version="1.0" encoding="UTF-8"?>
<x>
 <y Id="2" Name="Label1" Value="2760"/>
 <y Id="1" Name="Label" Value="2151"/>
 <y Id="14" Name="Label6" Value="193"/>
 <y Id="13" Name="Label5" Value="118"/>
 <y Id="25" Name="Label9" Value="100"/>
 <remainder Value="551"/>
</x>

Regards,

--A

From: "Darin" <darind@xxxxxxxxx> Reply-To: xsl-list@xxxxxxxxxxxxxxxxxxxxxx To: <xsl-list@xxxxxxxxxxxxxxxxxxxxxx> Subject: Grouping, Sorting on Aggegrated sum of the groupings, for Top XX Groupings Date: Mon, 13 Jun 2005 16:14:39 -0400

I am using MSXML 4.0 running on an IIS 5.0 server running classic ASP.

I'm trying to transform the xml below grouped by the Id attribute, and sort
(descending) on the aggregated sum(Value).  I'm also trying to limit the
amount of groupings to 6, where the first 5 groupings are the highest
aggregated sum values, and the 6th grouping is an aggregation of the
remaining groups.

Here is an example of the XML:

<x>
    <y Id="1" Name="Label" Value="2151"/>
    <y Id="2" Name="Label1" Value="2760"/>
    <y Id="2" Name="Label1" Value="50"/>
    <y Id="3" Name="Label2" Value="99"/>
    <y Id="4" Name="Label3" Value="9"/>
    <y Id="4" Name="Label3" Value="55"/>
    <y Id="5" Name="Label4" Value="56"/>
    <y Id="13" Name="Label5" Value="118"/>
    <y Id="14" Name="Label6" Value="193"/>
    <y Id="17" Name="Label7" Value="54"/>
    <y Id="23" Name="Label8" Value="76"/>
    <y Id="23" Name="Label8" Value="76"/>
    <y Id="23" Name="Label8" Value="76"/>
    <y Id="25" Name="Label9" Value="100"/>
</x>

I would like the xml to look like:

<x>
<!-- Summation of the Top 5 entries aggregated sum (Desc) -->
    <y Id="2" Name="Label1" Value="2810"/>
    <y Id="1" Name="Label" Value="2151"/>
    <y Id="23" Name="Label8" Value="228"/>
    <y Id="14" Name="Label6" Value="193"/>
    <y Id="13" Name="Label5" Value="118"/>

<!-- Summation of the remaining entries -->
    <y Id="" Name="Other" Value="373"/>

<!-- <y Id="3" Name="Label2" Value="99"/>
    <y Id="4" Name="Label3" Value="64"/>
    <y Id="5" Name="Label4" Value="56"/>
    <y Id="17" Name="Label7" Value="54"/>
    <y Id="25" Name="Label9" Value="100"/> -->
</x>


I have tried to use Keys and the XPath position function, but with no
success.

Thanks in an advance for any help!

Darin

_________________________________________________________________ Dont just search. Find. Check out the new MSN Search! http://search.msn.click-url.com/go/onm00200636ave/direct/01/

Current Thread
Grouping, Sorting on Aggegrated sum of the groupings, for Top XX Groupings Darin - 13 Jun 2005 20:13:42 -0000 Mukul Gandhi - 14 Jun 2005 03:39:16 -0000 Aron Bock - 14 Jun 2005 14:55:38 -0000 <=

<- Previous	Index	Next ->
Re: Grouping, Sorting on Agge, Mukul Gandhi	Thread	RE: Reading "entity reference, Mukul Gandhi
Node test problem with preced, Andreas Guther	Date	Re: Node test problem with pr, David Carlisle
	Month

PURCHASE STYLUS STUDIO ONLINE TODAY!

Purchasing Stylus Studio from our online shop is Easy, Secure and Value Priced!

Download The World's Best XML IDE!

Accelerate XML development with our award-winning XML IDE - Download a free trial today!

Subscribe in XML format

RSS 2.0
Atom 0.3

XML Editor - Download a 15 Day Free Trial Now >

See What's New in Stylus Studio >

Buy Stylus Studio - XML Editor - Now >