XML transformation using XSLT 1.0.multiple pass (group by, count^3, sort) final touch needed

Question

I think I've created some kind of Frankenstein's monster with this xslt, and I need some help to clean it up and add some more features. Reading up on previous examples I find them either too simple to be of any more help to guide me forward, or too complex for me to understand the sorcery taking place.

I'm having... Source.xml

<!-- language: lang-xml -->

<xml>
<data>
    <row arrived="2014-03-08" client="John" location="Venus" outcome="Won"/>
    <row arrived="2014-03-07" client="John" location="Venus" outcome="Lost"/>
    <row arrived="2014-03-07" client="Mark" location="Mars" outcome="Lost"/>
    <!-- note, date range filter in xslt, start... -->
    <row arrived="2014-03-06" client="Louie" location="Mars" outcome="Lost"/>
    <row arrived="2014-03-06" client="Jane" location="Venus" outcome="N/A"/>
    <row arrived="2014-03-04" client="John" location="Tellus" outcome="N/A"/>
    <row arrived="2014-03-04" client="Louie" location="Tellus" outcome="Won"/>
    <row arrived="2014-03-04" client="Steve" location="Tellus" outcome="Lost"/>
    <row arrived="2014-03-02" client="Mark" location="Mars" outcome="Won"/>
    <row arrived="2014-03-02" client="Olga" location="Saturnus" outcome="Lost"/>
    <!-- ...end -->
    <row arrived="2014-03-01" client="Louie" location="Saturnus" outcome="N/A"/>
    <row arrived="2014-03-01" client="Olga" location="Saturnus" outcome="Won"/>
</data>
</xml>

Pass-through... Stylesheet.xslt

<!-- language: lang-xml -->

<xsl:stylesheet version='1.0' xmlns:xsl='http://www.w3.org/1999/XSL/Transform'>
    <xsl:output method='xml' omit-xml-declaration='no' encoding='utf-8' indent='yes' />
    <xsl:param name='fromDate' select='20140302' />
    <xsl:param name='toDate' select='20140306' />

    <xsl:key name='location' match="data/row[((number(translate(@arrived, '-', '')) &gt;= $fromDate) and (number(translate(@arrived, '-', '')) &lt;= $toDate))]" use='@location' />

    <!-- I'm lost on this one, might not even be the best way to go about it -->
    <xsl:key name='won' match="data/row[@outcome='Won' and ((number(translate(@arrived, '-', '')) &gt;= $fromDate) and (number(translate(@arrived, '-', '')) &lt;= $toDate))]" use='@outcome' />

    <xsl:template match="/*">
        <locations>
        <xsl:for-each select="data/row[(count(. | key('location', @location)[1]) = 1) and ((number(translate(@arrived, '-', '')) &gt;= $fromDate) and (number(translate(@arrived, '-', '')) &lt;= $toDate))]">
            <xsl:sort select='@count' order='ascending' /> <!-- not working -->
            <location>
                <xsl:attribute name='name'>
                    <xsl:value-of select='@location' />
                </xsl:attribute>
                <xsl:attribute name='count'>
                    <xsl:value-of select="count(key('location', @location))"/>
                </xsl:attribute>
                <xsl:attribute name='won'>
                    <!--<xsl:value-of select="count(key('won', 'Won'))"/>-->
                    <!--<xsl:value-of select="count(data/row[@outcome='Won'])" />-->
                </xsl:attribute>
                <xsl:attribute name='lost'>
                    <!-- (the same way as won) -->
                </xsl:attribute>
            </location>
        </xsl:for-each>
        </locations>
    </xsl:template> 
</xsl:stylesheet>

Achieved / Results (AS-IS)

<!-- language: lang-xml -->

<?xml version="1.0" encoding="utf-8"?>
<locations>
   <location name="Mars" count="2" won="" lost=""/>
   <location name="Venus" count="1" won="" lost=""/>
   <location name="Tellus" count="3" won="" lost=""/>
   <location name="Saturnus" count="1" won="" lost=""/>
</locations>

1. Retrieved only <row>'s having @arrived within given date-range.
2. Group By the retrieved <row>'s (distinct, not unique) based on @location.
3. Added a new @count to <location> to count <row>@location-occurrences within the conditioned date range.

Missing / Help needed (+ TO-BE)

What's left to do...

• Get the count on @won + @lost working, (count only within the date range condition).
• A final sort of the resulting <location>'s on a given attribute, (@count, @name, @won or @lost). I.e some kind of <value sort name="$attribute" type="number|text" order="$order">

I am using http://xslttest.appspot.com/ to test.

Please help. Preferably with a simple enough and logic solution for me to understand and comprehend the magic happening. Also any clean up tips on what is ugly in the xslt is much appreciated.

Answer 1

See next:

<xsl:stylesheet version='1.0' xmlns:xsl='http://www.w3.org/1999/XSL/Transform'>
<xsl:output method='xml' omit-xml-declaration='no' encoding='utf-8' indent='yes' />
<xsl:param name='fromDate' select='20140302' />
<xsl:param name='toDate' select='20140306' />

<xsl:key name='location' match="data/row[((number(translate(@arrived, '-', '')) &gt;= $fromDate) and (number(translate(@arrived, '-', '')) &lt;= $toDate))]" use='@location' />

<!-- I'm lost on this one, might not even be the best way to go about it -->
<xsl:key name='won' match="data/row[@outcome='Won' and ((number(translate(@arrived, '-', '')) &gt;= $fromDate) and (number(translate(@arrived, '-', '')) &lt;= $toDate))]" use='@outcome' />

<xsl:template match="/*">
    <locations>
    <xsl:for-each select="data/row[(count(. | key('location', @location)[1]) = 1) and ((number(translate(@arrived, '-', '')) &gt;= $fromDate) and (number(translate(@arrived, '-', '')) &lt;= $toDate))]">
        <xsl:sort select="count(key('location', @location))" order='ascending' />
        <location>
            <xsl:variable name="currentLocation" select="@location" />
            <xsl:attribute name='name'>
                <xsl:value-of select="$currentLocation" />
            </xsl:attribute>
            <xsl:attribute name='count'>
                <xsl:value-of select="count(key('location', @location))"/>
            </xsl:attribute>
            <xsl:attribute name='won'>
                <xsl:value-of select="count(../row[@outcome= 'Won' and @location = $currentLocation and number(translate(@arrived, '-', '')) &gt;= $fromDate and number(translate(@arrived, '-', '')) &lt;= $toDate])" />
            </xsl:attribute>
            <xsl:attribute name='lost'>
                <xsl:value-of select="count(../row[@outcome= 'Lost' and @location = $currentLocation and number(translate(@arrived, '-', '')) &gt;= $fromDate and number(translate(@arrived, '-', '')) &lt;= $toDate])" />
            </xsl:attribute>
        </location>
    </xsl:for-each>
    </locations>
</xsl:template> 
</xsl:stylesheet>

Changes:

• You can't sort on @count since it is in the result, NOT in the input. You need to sort on the the way you count() it
• Added win/loss with the date range