Search code examples
xsltxslt-2.0xslt-grouping

How to resolve Group and Sort withing XSLT

I have the following input message where the data needs to be grouped based on 'EmpNo' and 'Date' first. But the result should be taken from the latest date time from field 'lastDateTime'

Input XML:

<Record>
<Record>
  <Date>2019-04-01T00:00:00.000</Date>
  <Hours>3</Hours>
  <EmpNo>825</EmpNo>
  <lastDateTime>2019-04-12T08:35:38.000</lastDateTime>
  <Rate>1139</Rate>
  <Code>102486</Code>
</Record>
<Record>
  <Date>2019-04-01T00:00:00.000</Date>
  <Hours>4</Hours>
  <EmpNo>826</EmpNo>
  <lastDateTime>2019-04-12T08:35:38.000</lastDateTime>
  <Rate>1139</Rate>
  <Code>102486</Code>
</Record>
<Record>
  <Date>2019-04-01T00:00:00.000</Date>
  <Hours>5</Hours>
  <EmpNo>827</EmpNo>
  <lastDateTime>2019-04-12T08:35:38.000</lastDateTime>
  <Rate>1139</Rate>
  <Code>102486</Code>
</Record>
<Record>
  <Date>2019-04-01T00:00:00.000</Date>
  <Hours>3</Hours>
  <EmpNo>825</EmpNo>
  <lastDateTime>2019-04-14T08:35:38.000</lastDateTime>
  <Rate>1139</Rate>
  <Code>102486</Code>
</Record>
<Record>
  <Date>2019-04-01T00:00:00.000</Date>
  <Hours>4</Hours>
  <EmpNo>826</EmpNo>
  <lastDateTime>2019-04-10T08:35:38.000</lastDateTime>
  <Rate>1139</Rate>
  <Code>102486</Code>
</Record>
<Record>
  <Date>2019-04-01T00:00:00.000</Date>
  <Hours>5</Hours>
  <EmpNo>827</EmpNo>
  <lastDateTime>2019-04-12T08:35:38.000</lastDateTime>
  <Rate>1139</Rate>
  <Code>102486</Code>
</Record>
<Record>
  <Date>2019-04-01T00:00:00.000</Date>
  <Hours>3</Hours>
  <EmpNo>825</EmpNo>
  <lastDateTime>2019-04-10T08:35:38.000</lastDateTime>
  <Rate>1139</Rate>
  <Code>102486</Code>
</Record>
<Record>
  <Date>2019-04-01T00:00:00.000</Date>
  <Hours>4</Hours>
  <EmpNo>826</EmpNo>
  <lastDateTime>2019-04-11T08:35:38.000</lastDateTime>
  <Rate>1139</Rate>
  <Code>102486</Code>
</Record>
<Record>
  <Date>2019-04-01T00:00:00.000</Date>
  <Hours>5</Hours>
  <EmpNo>827</EmpNo>
  <lastDateTime>2019-04-16T08:35:38.000</lastDateTime>
  <Rate>1139</Rate>
  <Code>102486</Code>
</Record>
</Record>

There could be many EmpNo with different Dates as well in the Input XML file. This needs to be grouped but it requires to extract only that particular node where lastDateTime is the latest.

Expected Result:

<Record>
<Record>
  <Date>2019-04-01T00:00:00.000</Date>
  <Hours>6</Hours>
  <EmpNo>825</EmpNo>
  <lastDateTime>2019-04-14T08:35:38.000</lastDateTime>
  <Rate>1142</Rate>
  <Code>13</Code>
</Record>
<Record>
  <Date>2019-04-01T00:00:00.000</Date>
  <Hours>4</Hours>
  <EmpNo>826</EmpNo>
  <lastDateTime>2019-04-12T08:35:38.000</lastDateTime>
  <Rate>1140</Rate>
  <Code>11</Code>
</Record>
<Record>
  <Date>2019-04-01T00:00:00.000</Date>
  <Hours>11</Hours>
  <EmpNo>827</EmpNo>
  <lastDateTime>2019-04-16T08:35:38.000</lastDateTime>
  <Rate>1147</Rate>
  <Code>18</Code>
</Record>
</Record>

I tried to write the following code but no luck.

<?xml version="1.0" encoding="UTF-8"?>
<xsl:transform xmlns:xsl="http://www.w3.org/1999/XSL/Transform" xmlns:xs="http://www.w3.org/2001/XMLSchema" xmlns:mf="http://example.com/mf"    exclude-result-prefixes="#all" version="2.0">

<xsl:output method="xml" encoding="utf-8" indent="no"/>
<xsl:template match="Record">
<xsl:copy>
    <xsl:for-each-group select="Record" group-by="EmpNo">
        <xsl:for-each-group select="current-group()" group-by="Date">
            <xsl:copy>
                <EmpNo>
                    <xsl:value-of select="EmpNo"/>
                </EmpNo>
                <Date>
                    <xsl:value-of select="Date"/>
                </Date>
                <lastDateTime>
                    <xsl:value-of select="max(current-group()/lastDateTime/xs:dateTime(.))"/>
                </lastDateTime>
                <Hours>
                    <xsl:value-of select="Hours[Date=max(current-group()/lastDateTime/xs:dateTime(.))]"/>
                </Hours>                
            </xsl:copy>
        </xsl:for-each-group>
    </xsl:for-each-group>
</xsl:copy>
</xsl:template>
</xsl:transform>
Solution

  • Based on your description I think you want

    <xsl:template match="Record">
<xsl:copy>
    <xsl:for-each-group select="Record" group-by="EmpNo">
        <xsl:for-each-group select="current-group()" group-by="Date">
            <xsl:for-each select="current-group()">
                <xsl:sort select="xs:dateTime(lastDateTime)"/>
                <xsl:if test="position() = last()">
                    <xsl:copy-of select="."/>
                </xsl:if>
            </xsl:for-each>
        </xsl:for-each-group>
    </xsl:for-each-group>
</xsl:copy>
</xsl:template>

    However, result at https://xsltfiddle.liberty-development.net/ncdD7mB is 

    <Record>
   <Record>
      <Date>2019-04-01T00:00:00.000</Date>
      <Hours>3</Hours>
      <EmpNo>825</EmpNo>
      <lastDateTime>2019-04-14T08:35:38.000</lastDateTime>
      <Rate>1139</Rate>
      <Code>102486</Code>
   </Record>
   <Record>
      <Date>2019-04-01T00:00:00.000</Date>
      <Hours>4</Hours>
      <EmpNo>826</EmpNo>
      <lastDateTime>2019-04-12T08:35:38.000</lastDateTime>
      <Rate>1139</Rate>
      <Code>102486</Code>
   </Record>
   <Record>
      <Date>2019-04-01T00:00:00.000</Date>
      <Hours>5</Hours>
      <EmpNo>827</EmpNo>
      <lastDateTime>2019-04-16T08:35:38.000</lastDateTime>
      <Rate>1139</Rate>
      <Code>102486</Code>
   </Record>
</Record>

    I am not sure from which item in a group you want to take the not grouped child elements.