How do I group by financial year in xslt?

Question

I have an XML of programmes and their premiere dates. As part of my XSLT for a report, I want to divide the results by financial year (July 1 - June 30). The final output will be to different tabs in an excel spreadsheet (but that's the easy bit). I used Munchean grouping to divide my years, but the output is based on the @year attribute in my data and I either lose the first or last financial year, depending on my for-each loop. Example Data (format is set by the exporting database):

<?xml version="1.0" encoding="UTF-8"?>
<slots>
    <SLOT oid="3229327812">
        <schedule_date>
            <DATE year="2016" month="6" day="1" monthname="June" dateindays="42155"/>
        </schedule_date>
        <schedule_channel>
            <CHANNEL name="Channel 1"/>
        </schedule_channel>
        <programme>
            <PROG_DETAIL prog_title="Amber Anchor"/>
        </programme>
    </SLOT>
    <SLOT oid="3229327813">
        <schedule_date>
            <DATE year="2016" month="6" day="30" monthname="June" dateindays="42184"/>
        </schedule_date>
        <schedule_channel>
            <CHANNEL name="Channel 1"/>
        </schedule_channel>
        <programme>
            <PROG_DETAIL prog_title="Big Bang"/>
        </programme>
    </SLOT>
    <SLOT oid="3229327815">
        <schedule_date>
            <DATE year="2016" month="7" day="30" monthname="July" dateindays="42214"/>
        </schedule_date>
        <schedule_channel>
            <CHANNEL name="Channel 1"/>
        </schedule_channel>
        <programme>
            <PROG_DETAIL prog_title="Car Crash"/>
        </programme>
    </SLOT>
    <SLOT oid="3229327814">
        <schedule_date>
            <DATE year="2016" month="7" day="1" monthname="July" dateindays="42185"/>
        </schedule_date>
        <schedule_channel>
            <CHANNEL name="Channel 2"/>
        </schedule_channel>
        <programme>
            <PROG_DETAIL prog_title="Deep Dodo"/>
        </programme>
    </SLOT>
    <SLOT oid="3229327815">
        <schedule_date>
            <DATE year="2017" month="1" day="5" monthname="January" dateindays="42365"/>
        </schedule_date>
        <schedule_channel>
            <CHANNEL name="Channel 2"/>
        </schedule_channel>
        <programme>
            <PROG_DETAIL prog_title="Eerie Earl"/>
        </programme>
    </SLOT>
    <SLOT oid="3229327815">
        <schedule_date>
            <DATE year="2017" month="7" day="5" monthname="July" dateindays="42531"/>
        </schedule_date>
        <schedule_channel>
            <CHANNEL name="Channel 1"/>
        </schedule_channel>
        <programme>
            <PROG_DETAIL prog_title="Fall Flat"/>
        </programme>
    </SLOT>
</slots>

XSLT:

<?xml version="1.0"?>
<xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform">
    <xsl:key name="kfyear1" use="@year" match="slots/SLOT/schedule_date/DATE[@month&lt;7]"/>
    <xsl:key name="kfyear2" use="@year" match="slots/SLOT/schedule_date/DATE[@month&gt;6]"/>
    <xsl:template match="/">
        <body>
            <FinancialYear>
            <!--<xsl:for-each select="/slots/SLOT/schedule_date/DATE[generate-id() = generate-id(key('kfyear1',@year)[1])]">-->
                <xsl:for-each select="/slots/SLOT/schedule_date/DATE[generate-id() = generate-id(key('kfyear2',@year)[1])]">
                    <xsl:variable name="fy" select="@year + (@month &gt; 6)"/>
                    <YEAR>
                        <xsl:text>Financial Year: </xsl:text><xsl:value-of select="$fy"/>
                    </YEAR>
                    <premieres>
                        <xsl:variable name="fy" select="@year + (@month &gt; 6)"/>
                        <xsl:call-template name="FinYear">
                            <xsl:with-param name="fyr" select="$fy"/>
                        </xsl:call-template>
                    </premieres>
                </xsl:for-each>
            </FinancialYear>
        </body>
    </xsl:template>
    <xsl:template name="FinYear">
        <xsl:param name="fyr"/>
        <xsl:for-each select="/slots/SLOT[(schedule_date/DATE/@year+(schedule_date/DATE/@month &gt; 6)=$fyr)]">
            <PREMIERE>
                <xsl:value-of select="schedule_channel/CHANNEL/@name"/><xsl:text>: </xsl:text>
                <xsl:value-of select="programme/PROG_DETAIL/@prog_title"/><xsl:text>: </xsl:text>
                <xsl:value-of select="schedule_date/DATE/@day"/><xsl:text> </xsl:text>
                <xsl:value-of select="schedule_date/DATE/@monthname"/><xsl:text> </xsl:text>
                <xsl:value-of select="schedule_date/DATE/@year"/>
            </PREMIERE>
        </xsl:for-each>
    </xsl:template>
</xsl:stylesheet>

Using the key 'kfyear1' in my for-each loop, I get financial years 2016 & 2017. Using the key 'kfyear2', I get financial years 2017 & 2018. How do I get all three financial years? I suspect that I need to abandon the for-each loop and do it all with templates, but I can't work out how.

EDIT: Adding expected results:

<?xml version="1.0"?>
<body>
    <FinancialYear>
        <YEAR>2016</YEAR>
        <premieres>
            <PREMIERE>Channel 1: Amber Anchor: 1 June 2016</PREMIERE>
            <PREMIERE>Channel 1: Big Bang: 30 June 2016</PREMIERE>
        </premieres>
    </FinancialYear>
    <FinancialYear>
        <YEAR>2017</YEAR>
        <premieres>
            <PREMIERE>Channel 1: Car Crash: 30 July 2016</PREMIERE>
            <PREMIERE>Channel 2: Deep Dodo: 1 July 2016</PREMIERE>
            <PREMIERE>Channel 2: Eerie Earl: 5 January 2017</PREMIERE>
        </premieres>
    </FinancialYear>
    <FinancialYear>
        <YEAR>2018</YEAR>
        <premieres>
            <PREMIERE>Channel 1: Fall Flat: 5 July 2017</PREMIERE>
        </premieres>
    </FinancialYear>
</body>

Tim C · Accepted Answer · 2016-05-16T16:15:07.037

You should only really need one key here, to group your dates by financial year

<xsl:key name="slot_by_year" match="DATE" use="@year + number(@month > 6)" />

So, both a year of 2016 and a month of 6, and a year of 2017 and a month of 5, have a "slot_by_year" value of 2016.

Then to get the distinct financial years, (by getting the first occurrence of the DATE for each financial year) you would do this

<xsl:for-each select="SLOT/schedule_date/DATE[generate-id() = generate-id(key('slot_by_year', @year + number(@month > 6))[1])]">

And then to get the SLOT elements within this financial year, you would do this (where $fy is set to the current financial year)

<xsl:apply-templates select="key('slot_by_year', $fy)/ancestor::SLOT"/>

Try this XSLT

<xsl:stylesheet xmlns:xsl="http://www.w3.org/1999/XSL/Transform" version="2.0">
  <xsl:output method="xml" indent="yes" />
  <xsl:key name="slot_by_year" match="DATE" use="@year + number(@month > 6)" />

  <xsl:template match="/slots">
    <body>
      <xsl:for-each select="SLOT/schedule_date/DATE[generate-id() = generate-id(key('slot_by_year', @year + number(@month > 6))[1])]">
        <xsl:variable name="fy" select="@year + number(@month > 6)"/>
        <financial_year>
          <YEAR><xsl:value-of select="$fy" /></YEAR>
          <premieres>
            <xsl:apply-templates select="key('slot_by_year', $fy)/ancestor::SLOT"/>
          </premieres>
        </financial_year>
      </xsl:for-each>
    </body>
  </xsl:template>

  <xsl:template match="SLOT">
    <PREMIERE>
      <xsl:value-of select="schedule_channel/CHANNEL/@name"/>
      <xsl:text>: </xsl:text>
      <xsl:value-of select="programme/PROG_DETAIL/@prog_title"/>
    </PREMIERE>
  </xsl:template>
</xsl:stylesheet>

Thanks Tim, that helps with the template stuff. Unfortunately, as written the XSLT groups the 2016 & 2017 financial years together as one year. — Hockney, May 16 '16 at 16:11
My apologies. I had the financial year starting in June, not July. I've corrected my answer! — Tim C, May 16 '16 at 16:16
Aha! Thanks Tim - So you're using the key to set the financial year correctly (which I was trying to do but failed) and then the ancestor to pull it together. I need to get my head around xpath axes. — Hockney, May 16 '16 at 16:26

How do I group by financial year in xslt?

1 Answers1