Getting unique values from substring

rabs · February 23rd, 2009, 08:56 AM

I have a tricky requirement.

I need to sort some data based on a month value which is substring of a date node. I'm trying to figure out a way to retrieve the unique values for the substring "month" since I am not able to use preceding-sibling to get it.

My xslt is as follows.....

<xsl:for-each select="$dcr">
<xsl:sort select="month" order="descending"/>
<xsl:variable name="yearbf" select="substring-after(date,'/')"/>
<xsl:variable name="year" select="substring-after($yearbf,'/')"/>
<xsl:variable name="month" select="substring-before(date,'/')"/>

<xsl:choose>
<xsl:when test="$date = $year">
<tr>
<td align="top" valign="middle">
<b><xsl:value-of select="$month"/></b><br/>
<a href="$PAGE_LINK[/press-releases/detail]dcr={@dcr}"><xsl:value-of select="title"/></a><br/>
</td>
</tr>
</xsl:when>
</xsl:choose>
</xsl:for-each>

My xml is as follows....

<PR dcr="/test/PR/data/2008/05/2008-05-27">
<date>05/27/2008</date>

<title>PR title</title>

<content>
</PR>

Any help would be appreciated.

mhkay · February 23rd, 2009, 09:11 AM

If you're in XSLT 2.0,

distinct-values($dcr/substring-before(date, '/'))

If you're in XSLT 1.0, you'll have to use Muenchian grouping.

Martin Honnen · February 23rd, 2009, 09:12 AM

Consider to group on the month value you extract from the date element.

rabs · February 23rd, 2009, 09:28 AM

I'm using xslt 1.0. and getting an error when I use the fucntion-distinct-values.

rabs · February 23rd, 2009, 09:48 AM

Quote:

Consider to group on the month value you extract from the date element.

Any tips on this?

Martin Honnen · February 23rd, 2009, 10:05 AM

Here is an example using Muenchian grouping.
Assuming the XML input is as follows:

Code:

<root>
<PR dcr="/test/PR/data/2008/05/2008-05-27">
<date>05/27/2008</date>

<title>PR title</title>

</PR>
<PR dcr="/test/PR/data/2008/05/2008-05-27">
<date>05/27/2008</date>

<title>foo</title>

</PR>
<PR dcr="/test/PR/data/2008/05/2008-05-27">
<date>05/27/2008</date>

<title>bar</title>

</PR>

<PR dcr="/test/PR/data/2008/05/2008-05-27">
<date>12/27/2008</date>

<title>foo</title>

</PR>
</root>

then the following stylesheet

Code:

<xsl:stylesheet
  xmlns:xsl="http://www.w3.org/1999/XSL/Transform"
  version="1.0">
  
  <xsl:output method="html" indent="yes"/>
  
  <xsl:key name="by-month" match="PR" use="substring-before(date, '/')"/>
  
  <xsl:template match="root">
    <html>
      <head>
        <title>Example</title>
      </head>
      <body>
        <table border="1">
          <xsl:apply-templates select="PR[generate-id() = generate-id(key('by-month', substring-before(date, '/'))[1])]" mode="group"/>
        </table>
      </body>
    </html>
  </xsl:template>
  
  <xsl:template match="PR" mode="group">
    <xsl:variable name="cg" select="key('by-month', substring-before(date, '/'))"/>
    <tr>
      <td rowspan="{count($cg)}">
        <xsl:value-of select="substring-before(date, '/')"/>
      </td>
      <td>
        <xsl:value-of select="title"/>
      </td>
    </tr>
    <xsl:apply-templates select="$cg[position() &gt; 1]"/>
  </xsl:template>
  
  <xsl:template match="PR">
    <tr>
      <td>
        <xsl:value-of select="title"/>
      </td>
    </tr>
  </xsl:template>

</xsl:stylesheet>

outputs the following HTML:

Code:

<html>
   <head>
      <meta http-equiv="Content-Type" content="text/html; charset=utf-8">

      <title>Example</title>
   </head>
   <body>
      <table border="1">
         <tr>
            <td rowspan="3">05</td>
            <td>PR title</td>
         </tr>
         <tr>
            <td>foo</td>
         </tr>
         <tr>
            <td>bar</td>
         </tr>
         <tr>
            <td rowspan="1">12</td>
            <td>foo</td>
         </tr>
      </table>
   </body>
</html>

rabs · February 23rd, 2009, 10:14 AM

Thanks. Not too sound entirely stupid - I'm quite new to this, I'm in a bit of a loss as to how to use your solution in my code..

I'll post the entire code....

Code:

<!DOCTYPE html-entities SYSTEM "http://www.interwoven.com/livesite/xsl/xsl-html.dtd">
<xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform"
  xmlns:datetime="http://exslt.org/dates-and-times" exclude-result-prefixes="datetime">
  <!-- Skin: Default (Default Browser Type) -->
  
  <xsl:variable name="dcr" select="/Properties/Data/Result/Externals/PRList[@method='getAllPrDCRsByType']/PressRelease" />
  <xsl:variable name="year" select="/Properties/Data/External/Parameters/Datum[@Name='year']"/>

<xsl:variable name="date">
<xsl:choose>
 <xsl:when test="$year = ''">
  <xsl:value-of select="datetime:year()"/> 
</xsl:when>
<xsl:otherwise>
  <xsl:value-of select="$year"/>
</xsl:otherwise>
</xsl:choose> 
</xsl:variable> 
  <xsl:include href="http://www.interwoven.com/livesite/xsl/HTMLTemplates.xsl"/>
  <xsl:include href="http://www.interwoven.com/livesite/xsl/StringTemplates.xsl"/>
  

  <xsl:template match="/">
    
    <div>
      <table width="545" cellspacing="0" cellpadding="0">
        <tr>
          <td width="2" valign="top" height="2">
            <img src="$URL_PREFIX/assets/images/cornerImages/top_corner_left.png"/>
          </td>
     <td colspan="3" height="2" style="background-color:#C3D3DF">
     </td>
     <td width="2" valign="top" height="2">
       <img src="$URL_PREFIX/assets/images/cornerImages/top_corner_right.png"/>
     </td> 
        </tr>
        <tr>
          <td style="background-color:#C3D3DF">
     </td>
     <td colspan="3" style="background-color:#C3D3DF" class="MarkitSubHeading">&nbsp;&nbsp;&nbsp;<xsl:value-of select="$date"/>&nbsp;Press Releases</td>
     <td style="background-color:#C3D3DF"></td>
        </tr>
        <tr>
          <td colspan="5" style="background-color:#C3D3DF" height="7"></td>
        </tr>
      </table>
      <table width="545" cellpadding="0" cellspacing="0" style="font-family:Arial, Helvetica, sans-serif; font-size: 8pt;">
        <tr>
          <td>
       <img src="$URL_PREFIX/assets/images/Bottom.gif" width="2" height="100%"/>
     </td>
     <td height="12" colspan="3">&nbsp;</td>
     <td align="right">
       <img src="$URL_PREFIX/assets/images/Bottom.gif" width="2" height="100%"/>
     </td> 
        </tr>
        <tr>
          <td>
       <img src="$URL_PREFIX/assets/images/Bottom.gif" width="2" height="100%"/>
     </td>
     <td width="12">
     </td> 
     <td valign="top" >  
       <table width="521" border="0" style="font-family:Arial, Helvetica, sans-serif; font-size: 8pt;">
              <xsl:for-each select="$dcr">
            <xsl:sort select="month" order="descending"/>
            <xsl:variable name="yearbf" select="substring-after(date,'/')"/>
            <xsl:variable name="year" select="substring-after($yearbf,'/')"/>
            <xsl:variable name="month" select="substring-before(date,'/')"/>
            <xsl:variable name="unique-date" select="date[not(preceding-sibling::date)]"/> 

            <xsl:choose> 
            <xsl:when test="$date = $year">
            <tr>
              <td align="top" valign="middle">
               <!--b><xsl:value-of select="$month"/></b><br/-->
               <b><xsl:value-of select="$unique-date"/></b><br/>
               <a href="$PAGE_LINK[/press-releases/detail]dcr={@dcr}"><xsl:value-of select="title"/></a><br/>
               </td>
            </tr>
            </xsl:when>
            </xsl:choose> 
            </xsl:for-each>

       </table>
          </td>
          <td width="12">
          </td> 
     <td align="right" height="2">
       <img src="$URL_PREFIX/assets/images/Bottom.gif" width="2" height="100%"/>
     </td>
        </tr>
        <tr>
          <td height="1">
       <img src="$URL_PREFIX/assets/images/cornerImages/bottom_corner_left.png"/>
     </td>
     <td colspan="3" valign="bottom">
       <img src="$URL_PREFIX/assets/images/Bottom.gif" width="100%" height="2"/>
     </td>
     <td height="1">
       <img src="$URL_PREFIX/assets/images/cornerImages/bottom_corner_right.png"/>
     </td>
        </tr>
      </table> 
    </div>
  </xsl:template>
  
</xsl:stylesheet>

Martin Honnen · February 23rd, 2009, 10:56 AM

It is hard to guess how your XML looks. Based on your variable you will need a key as follows:

Code:

<xsl:key name="by-month" mach="PressRelease" use="substring-before(date, '/')"/>

Then you need to replace your xsl:for-each select="$dcr" with e.g.

Code:

<xsl:for-each select="$dcr[generate-id() = generate-id(key('by-month', substring-before(date, '/'))[1])]">
  <xsl:sort select="substring-before(date, '/')" order="descending"/>

to process only those PressRelease elements with unique month values.

If you want to understand Muenchian grouping then read http://www.jenitennison.com/xslt/grouping/muenchian.xml

rabs · February 25th, 2009, 03:51 AM

Following on from the post above..

I am attempting to get the unique years using the example you sent....

I have created a key as follows.....

Code:

<xsl:key name="by-year" match="/Properties/Data/Result/PRList/PressRelease" use="substring(date,7,4)"/>

my xsl is as follows...

Code:

<xsl:for-each select="$dcr[generate-id() = generate-id(key('by-year', substring(date,7,4)[1])]">                
                      <xsl:value-of select="substring(date,7,4)"/><br/>
                    </xsl:for-each>

But I am getting the following error message...

Quote:

xml.ValidationError mColumn[117] mLine[57] mSystemID[null] mPublicID[null] mType[FATAL] mMessage[Expected ,, but found: ]]

I am using xslt 1.0 by the way. Thanks.

mhkay · February 25th, 2009, 04:38 AM

Just check the nesting of brackets in that select expression.