Adding values from XML nodes based on first and last dates using XSLT

Question

I have an XML file that has several sibling nodes, one which stores timestamps <TIMESTAMP>, another with categorical codes <Code>, and another with integer values <Count>. I want to calculate the difference in <Count> values, per <Code>, associated with the first and last <TIMESTAMP>. There are multiple codes... I have simplified the XML quite a bit. The result of this calculation will then populate one of two cells in an HTML table. I am restricted to using XSLT 1.0 to transform the XML to HTML.

Here is a snippet of the XML:

XML

<?xml version="1.0" encoding="UTF-8"?>
<surveyGDB>
  <table>
    <tablename>QaQcPoints
      <record>
        <OBJECTID>1</OBJECTID>
        <TIMESTAMP>20150722 09:18:43</TIMESTAMP>
        <Code>tp</Code>
        <Count>50</Count>
      </record>
      <record>
        <OBJECTID>2</OBJECTID>
        <TIMESTAMP>20150722 09:18:43</TIMESTAMP>
        <Code>bl</Code>
        <Count>110</Count>
      </record>
      <record>
        <OBJECTID>5</OBJECTID>
        <TIMESTAMP>20150730 01:05:22</TIMESTAMP>
        <Code>bl</Code>
        <Count>100</Count>
      </record>
      <record>
        <OBJECTID>6</OBJECTID>
        <TIMESTAMP>20150730 01:05:22</TIMESTAMP>
        <Code>tp</Code>
        <Count>55</Count>
      </record>
    </tablename>
  </table>
</surveyGDB>

The output HTML table should look something like this:

HTML

----------------------------
| Code   | Added | Deleted |
----------------------------
| tp     | 5     | 0       |
----------------------------
| bl     | 0     | 10      |
----------------------------

I assume I need to sort the <TIMESTAMP> values, and find the first and last positions of the sorted values. Here's the relevant XSL code I have for finding the <Count> value associated with the earliest timestamp:

XSL

<xsl:for-each select="/surveyGDB/table/tablename/record" />
    <xsl:sort select="TIMESTAMP" />
        <xsl:choose>
            <xsl:when test="position() = 1">
                <xsl:value-of select="Count" />
            </xsl:when>
        </xsl:choose>

But how do I also find the last timestamp, and then how do I then group the calculated <Count> values by the <Code>?

Any advice on how to best approach this?

hr_117 hr_117 · Accepted Answer · 2016-04-13T05:13:50

Start with a look for muenchian grouping

And try something like this:

<xsl:key name="krecord" match="record" use="Code"/>

<xsl:template match="tablename">
  <xsl:for-each select="record [ 
                        count ( key('krecord',./Code)[1] | . ) = 1]">

    <xsl:variable name="this" select="." />
    <!-- group stuff -->

    <xsl:for-each select=" key('krecord',$this/Code)">
      <!-- group member stuff -->

    </xsl:for-each>  
  </xsl:for-each>
</xsl:template>

And than add the first, last, add diff "calculation" for a basic idea try:

<xsl:key name="krecord" match="record" use="Code"/>

<xsl:template match="tablename">
    <xsl:for-each select="record [ 
                          count ( key('krecord',./Code)[1] | . ) = 1]">

      <xsl:variable name="this" select="." />
      <!-- group stuff -->
      <xsl:variable name="first" >
        <xsl:for-each select=" key('krecord',$this/Code)">
          <xsl:sort select="TIMESTAMP" />
          <xsl:choose>
              <xsl:when test="position() = 1">
                  <xsl:value-of select="Count" />
              </xsl:when>
          </xsl:choose>
        </xsl:for-each>
      </xsl:variable>

      <xsl:variable name="last" >
        <xsl:for-each select=" key('krecord',$this/Code)">
          <xsl:sort select="TIMESTAMP" />
          <xsl:choose>
              <xsl:when test="position() = last()">
                  <xsl:value-of select="Count" />
              </xsl:when>
          </xsl:choose>
        </xsl:for-each>
      </xsl:variable>

      <xsl:variable name="diff" select="$first - $last " />
      <xsl:choose>
        <xsl:when test="$diff >= 0" >
              <xsl:value-of select="Code" />, <xsl:value-of select="$diff" />,0;    
        </xsl:when>
        <xsl:otherwise>
          <xsl:value-of select="Code" />, 0, <xsl:value-of select="$diff" />;
        </xsl:otherwise>       
      </xsl:choose>

  </xsl:for-each>
</xsl:template>

Adding values from XML nodes based on first and last dates using XSLT

1 Answers