Re: [xsl] sorting by date

Subject: Re: [xsl] sorting by date
From: Jeni Tennison <mail@xxxxxxxxxxxxxxxx>
Date: Tue, 24 Jul 2001 16:44:14 +0100
Hi Daniel,

You seem to have two problems rolled into one:

  * iterating over the items in date order
  * keeping a running total of the balance while iterating over them

Both are fairly easy on their own, but are a bit of a nightmare in
combination. Therefore, it would probably be best for your sanity if
you split the transformation into the two separate parts and dealt
with them individually. So you could sort the Item elements in one
step:

<xsl:template match="RetrieveTransactionHistory2RSResponse">
  <xsl:variable name="sorted-items">
    <xsl:for-each select="Items/Item">
      <xsl:sort select="concat(substring(Date, 5),
                               substring(Date, 3, 2),
                               substring(Date, 1, 2))"
                order="descending" />
      <xsl:copy-of select="." />
    </xsl:for-each>
  </xsl:variable>
  ...
</xsl:template>

Then you could iterate over these Item elements to produce the output
that you want, safe in the knowledge that they're in the correct order
to start with, using your GetTableData template or something similar,
(personally I'd apply templates to the first Item element and use a
template that steps through them one by one with something like):

<xsl:template match="RetrieveTransactionHistory2RSResponse">
  <xsl:variable name="sorted-items">
    <xsl:for-each select="Items/Item">
      <xsl:sort select="concat(substring(Date, 5),
                               substring(Date, 3, 2),
                               substring(Date, 1, 2))"
                order="descending" />
      <xsl:copy-of select="." />
    </xsl:for-each>
  </xsl:variable>
  <table>
    <xsl:apply-templates select="exsl:node-set($sorted-items)/Item[1]">
      <xsl:with-param name="CurrentBalance"
                      select="$BaseBalance" />
    </xsl:apply-templates>
  </table>
</xsl:template>

<xsl:template match="Item">
  <xsl:param name="CurrentBalance" />
  <xsl:param name="Index" select="1" />
  <xsl:variable name="TestValue"
                select="UnitsTraded[../UnitsTraded != 0] |
                        MoneyTrade[../UnitsTraded = 0]" />
  <tr valign="top">
    <xsl:call-template name="GenerateRowColor">
      <xsl:with-param name="Index" select="$Index" />
    </xsl:call-template>
    <td width="10%">
      <xsl:value-of select="concat(substring(Date, 1, 2), '/',
                                   substring(Date, 3, 2), '/',
                                   substring(Date, 5))" />
    </td>
    <td width="50%" nowrap="nowrap">
      <xsl:value-of select="Description" />
    </td>
    ... as in your ShowRunningBalance template ...
  </tr>
  <xsl:apply-templates select="following-sibling::Item[1]">
    <xsl:with-param name="CurrentBalance"
                    select="$CurrentBalance - $UnitsTraded" />
    <xsl:with-param name="Index" select="$Index + 1" />
  </xsl:apply-templates>
</xsl:template>

If you're not prepared to use a node-set() extension function (in the
above I've used the one from EXSLT) then things are a bit different. I
think the easiest thing to do is to forget about keeping
track of the current balance, but instead calculate it for each Item
by finding all those Items with a Date after this one (i.e. with
values that should be subtracted from the current balance). That way
you can apply templates to all the Item elements in parallel, and use
a sort within the xsl:apply-templates:

<xsl:template match="RetrieveTransactionHistory2RSResponse">
  <table>
    <xsl:apply-templates select="Items/Item">
      <xsl:sort select="concat(substring(Date, 5),
                               substring(Date, 3, 2),
                               substring(Date, 1, 2))"
                order="descending" />
    </xsl:apply-templates>
  </table>
</xsl:template>

<xsl:template match="Item">
  <xsl:variable name="Date" select="Date" />
  <xsl:variable name="FollowingItems"
                select="../Item[concat(substring(Date, 5),
                                       substring(Date, 3, 2),
                                       substring(Date, 1, 2)) >
                                concat(substring($Date, 5),
                                       substring($Date, 3, 2),
                                       substring($Date, 1, 2))]" />
  <xsl:variable name="FollowingItemsBalance"
                select="sum($FollowingItems[UnitsTraded != 0]
                              /UnitsTraded |
                            $FollowingItems[UnitsTraded = 0]
                              /MoneyTrade)" />
  <xsl:variable name="CurrentBalance"
                select="$BaseBalance - $FollowingItemsBalance" />
  <tr valign="top">
    <xsl:call-template name="GenerateRowColor">
      <xsl:with-param name="Index" select="position()" />
    </xsl:call-template>
    <td width="10%">
      <xsl:value-of select="concat(substring($Date, 1, 2), '/',
                                   substring($Date, 3, 2), '/',
                                   substring($Date, 5))" />
    </td>
    <td width="50%" nowrap="nowrap">
      <xsl:value-of select="Description" />
    </td>
    ... as in your ShowRunningBalance template ...
  </tr>
</xsl:template>

Note that the above needs a BaseBalance global variable to hold what
you have in one of your templates as:

  HoldingInformation2Response/Items
                             /Item[class_code = $ClassCode]/Holding

This isn't shown in your source XML, so I don't know what the variable
definition should actually look like.

The disadvantage of this method is that you are constantly collecting
nodes with following dates and recalculating the balance, which means
that it won't be as efficient. I *think* that it will only matter if
you have a lot of Item elements, but you probably want to try both
methods to find which works best with your source XML, your processor,
your platform and so on.

Do let me know if you've got any questions about the code above.

Cheers,

Jeni

---
Jeni Tennison
http://www.jenitennison.com/


 XSL-List info and archive:  http://www.mulberrytech.com/xsl/xsl-list


Current Thread