Sorting Problem

cooltechie · July 30th, 2010, 02:44 AM

Hi,

I have an input XML:

<Items>
<Item>
<Account>123</Account>
<BillDate>10/12/2009</BillDate>
<currentConsumption>30456</currentConsumption>
<PreviousMonthConsumption>12345</PreviousMonthConsumption>
<PercentChange>59.4</PercentChange>
</Item>

<Item>
<Account>123</Account>
<BillDate>9/12/2009</BillDate>
<currentConsumption>12345</currentConsumption>
<PreviousMonthConsumption>8345</PreviousMonthConsumption>
<PercentChange>48</PercentChange>
</Item>

<Item>
<Account>123</Account>
<BillDate>8/12/2009</BillDate>
<currentConsumption>8345</currentConsumption>
<PreviousMonthConsumption>6000</PreviousMonthConsumption>
<PercentChange>28.1</PercentChange>
</Item>

<Item>
<Account>456</Account>
<BillDate>10/12/2009</BillDate>
<currentConsumption>20000</currentConsumption>
<PreviousMonthConsumption>10000</PreviousMonthConsumption>
<PercentChange>50</PercentChange>
</Item>

<Item>
<Account>456</Account>
<BillDate>9/12/2009</BillDate>
<currentConsumption>10000</currentConsumption>
<PreviousMonthConsumption>5000</PreviousMonthConsumption>
<PercentChange>50</PercentChange>
</Item>

<Item>
<Account>456</Account>
<BillDate>8/12/2009</BillDate>
<currentConsumption>5000</currentConsumption>
<PreviousMonthConsumption>6000</PreviousMonthConsumption>
<PercentChange>-20</PercentChange>
</Item>

</Items>

What I am looking for is : For each Each Account, I would like to get the latest bill (this is based on the Bill Date), and then get its associated Details (for eg: currentConsumption, PreviousMonthConsumption and PercentChange) and then Rank all Accounts based on the percentChange. In this case..a negative PercentChange should be given highest Rank.

OutputXML:

<table>
<tr>Account</th>
<th>currentConsumption</th>
<th>Previous Consumption</th>
<th>percent Change</th>
<th>Rank</th>
</tr>

<tr>
<td>456</td>
<td>20000</td>
<td>10000</td>
<td>50</td>
<td>1</td>
</tr>
<tr>
<td>123</td>
<td>30456</td>
<td>12345</td>
<td>59.4</td>
<td>2</td>
</tr>
</table>

I am using xslt 1.1 and not sure how to achieve this? Can anyone please help?

Thanks,

mhkay · July 30th, 2010, 03:50 AM

You haven't said whether your dates are d/m/y or m/d/y - your examples are all ambiguous. It's best to avoid this problem by using the international format yyyy-mm-dd. If you do that, you can also take advantage of XSLT's intrinsic support for date/time handling.

If you're using "XSLT 1.1" then it's been obsolete for about 10 years (it never got past working draft stage) - time to move to XSLT 2.0.

cooltechie · July 30th, 2010, 06:57 AM

Sorry Mike,

the dates are m/d/yyyy format and I am stuck with using xslt 1

Martin Honnen · July 30th, 2010, 07:28 AM

Here is an XSLT 1.0 stylesheet that groups and sorts. It needs an extension function like exsl:node-set however to convert a temporary result in the form of a result tree fragment into a node-set:

Code:

<xsl:stylesheet
  xmlns:xsl="http://www.w3.org/1999/XSL/Transform"
  xmlns:exsl="http://exslt.org/common"
  version="1.0">
  
  <xsl:output method="html" indent="yes"/>
  
  <xsl:key name="k1" match="Item" use="Account"/>
  
  <xsl:template match="Items">
    <table>
      <thead>
        <tr>
          <th>Account</th>
          <th>currentConsumption</th>
          <th>Previous Consumption</th>
          <th>percent Change</th>
          <th>Rank</th>
        </tr>
      </thead>
      <tbody>
        <xsl:variable name="latest">
          <xsl:for-each select="Item[generate-id() = generate-id(key('k1', Account)[1])]">
            <xsl:for-each select="key('k1', Account)">
              <xsl:sort select="substring-after(substring-after(BillDate, '/'), '/')" data-type="number" order="descending"/>
              <xsl:sort select="substring-before(BillDate, '/')" data-type="number" order="descending"/>
              <xsl:sort select="substring-before(substring-after(BillDate, '/'), '/')" data-type="number" order="descending"/>
              <xsl:if test="position() = 1">
                <xsl:copy-of select="."/>
              </xsl:if>
            </xsl:for-each>
          </xsl:for-each>
        </xsl:variable>
        <xsl:for-each select="exsl:node-set($latest)/Item">
          <xsl:sort select="PercentChange" data-type="number" order="ascending"/>
          <tr>
            <xsl:apply-templates select="Account | currentConsumption | PreviousMonthConsumption | PercentChange"/>
            <td><xsl:value-of select="position()"/></td>
          </tr>
        </xsl:for-each>
      </tbody>
    </table>
  </xsl:template>
  
  <xsl:template match="Item/*">
    <td><xsl:value-of select="."/></td>
  </xsl:template>

</xsl:stylesheet>

mhkay · July 30th, 2010, 07:40 AM

>I am stuck with using xslt 1

If you are using "XSLT 1.1" (which was only implemented in two Java processors, Saxon 6.5 and jd.xslt) then I find it hard to imagine why you are stuck with XSLT 1.

cooltechie · July 30th, 2010, 08:35 AM

Hi Martin,

Thanks for the reply. this seems to work..but for some reason the output is all gnerated outside table element. I checked the HTML in the XSL and nothing seems to be worng with it.

Martin Honnen · July 30th, 2010, 08:44 AM

I don't understand what "output is all gnerated outside table element" exactly means. Can you a sample?
When I use Saxon 6.5.5 with

Code:

<xsl:stylesheet
  xmlns:xsl="http://www.w3.org/1999/XSL/Transform"
  xmlns:exsl="http://exslt.org/common"
  exclude-result-prefixes="exsl"
  version="1.0">
  
  <xsl:output method="html" indent="yes"/>
  
  <xsl:key name="k1" match="Item" use="Account"/>
  
  <xsl:template match="Items">
    <table>
      <thead>
        <tr>
          <th>Account</th>
          <th>currentConsumption</th>
          <th>Previous Consumption</th>
          <th>percent Change</th>
          <th>Rank</th>
        </tr>
      </thead>
      <tbody>
        <xsl:variable name="latest">
          <xsl:for-each select="Item[generate-id() = generate-id(key('k1', Account)[1])]">
            <xsl:for-each select="key('k1', Account)">
              <xsl:sort select="substring-after(substring-after(BillDate, '/'), '/')" data-type="number" order="descending"/>
              <xsl:sort select="substring-before(BillDate, '/')" data-type="number" order="descending"/>
              <xsl:sort select="substring-before(substring-after(BillDate, '/'), '/')" data-type="number" order="descending"/>
              <xsl:if test="position() = 1">
                <xsl:copy-of select="."/>
              </xsl:if>
            </xsl:for-each>
          </xsl:for-each>
        </xsl:variable>
        <xsl:for-each select="exsl:node-set($latest)/Item">
          <xsl:sort select="PercentChange" data-type="number" order="ascending"/>
          <tr>
            <xsl:apply-templates select="Account | currentConsumption | PreviousMonthConsumption | PercentChange"/>
            <td><xsl:value-of select="position()"/></td>
          </tr>
        </xsl:for-each>
      </tbody>
    </table>
  </xsl:template>
  
  <xsl:template match="Item/*">
    <td><xsl:value-of select="."/></td>
  </xsl:template>

</xsl:stylesheet>

against

Code:

<Items>
<Item>
<Account>123</Account>
<BillDate>10/12/2009</BillDate>
<currentConsumption>30456</currentConsumption>
<PreviousMonthConsumption>12345</PreviousMonthConsumption>
<PercentChange>59.4</PercentChange>
</Item>

<Item>
<Account>123</Account>
<BillDate>9/12/2009</BillDate>
<currentConsumption>12345</currentConsumption>
<PreviousMonthConsumption>8345</PreviousMonthConsumption>
<PercentChange>48</PercentChange>
</Item>

<Item>
<Account>123</Account>
<BillDate>8/12/2009</BillDate>
<currentConsumption>8345</currentConsumption>
<PreviousMonthConsumption>6000</PreviousMonthConsumption>
<PercentChange>28.1</PercentChange>
</Item>

<Item>
<Account>456</Account>
<BillDate>10/12/2009</BillDate>
<currentConsumption>20000</currentConsumption>
<PreviousMonthConsumption>10000</PreviousMonthConsumption>
<PercentChange>50</PercentChange>
</Item>

<Item>
<Account>456</Account>
<BillDate>9/12/2009</BillDate>
<currentConsumption>10000</currentConsumption>
<PreviousMonthConsumption>5000</PreviousMonthConsumption>
<PercentChange>50</PercentChange>
</Item>

<Item>
<Account>456</Account>
<BillDate>8/12/2009</BillDate>
<currentConsumption>5000</currentConsumption>
<PreviousMonthConsumption>6000</PreviousMonthConsumption>
<PercentChange>-20</PercentChange>
</Item>

</Items>

it outputs

Code:

<table>
   <thead>
      <tr>
         <th>Account</th>
         <th>currentConsumption</th>
         <th>Previous Consumption</th>
         <th>percent Change</th>
         <th>Rank</th>
      </tr>
   </thead>
   <tbody>
      <tr>
         <td>456</td>
         <td>20000</td>
         <td>10000</td>
         <td>50</td>
         <td>1</td>
      </tr>
      <tr>
         <td>123</td>
         <td>30456</td>
         <td>12345</td>
         <td>59.4</td>
         <td>2</td>
      </tr>
   </tbody>
</table>

cooltechie · July 30th, 2010, 09:08 AM

Hi Martin,

am getting following output. That is it is copying all the Values outside Table except for the Rank. which is output in the first column.

456200001000050
123304561234559.4
<table>
<thead>
<tr>
<th>Account</th>
<th>currentConsumption</th>
<th>Previous Consumption</th>
<th>percent Change</th>
<th>Rank</th>
</tr>
</thead>
<tbody>
<tr>
<td>1</td>
<td></td>
<td></td>
<td></td>
<td></td>
</tr>
<tr>
<td>2</td>
<td></td>
<td></td>
<td></td>
<td></td>
</tr>
</tbody>
</table>

mhkay · July 30th, 2010, 09:21 AM

That's completely weird. What XSLT processor are you using, and how are you running it?

cooltechie · July 30th, 2010, 09:33 AM

Hi Mike,

for the Client side it is Internet Explorer.