CODEWITHSUNDEEP
xsltxslt-2.0
cel
cel

Reputation: 21

xslt - How to output data for a series of dates when some dates not in source xml

I am new to xslt and after many attempts am not able to solve the below.

I have an xml data file which contains data I cannot change. For example the below simple xml file:

<?xml version="1.0" encoding="UTF-8"?>
<sales>
    <sale>
        <year>2012</year>
        <amount>200</amount>
    </sale>
    <sale>
        <year>2012</year>
        <amount>180</amount>
    </sale>
    <sale>
        <year>2010</year>
        <amount>120</amount>
    </sale>
</sales>

I want to group the sale data on year and sum the amount so I get a total amount per year which I can do ok.

There is data for 2012 and 2010 but not for 2011 in the xml file.

I would like my output to have a figure of 0 for 2011 so I have output for all years 2010,2011 and 2012.

I am actually going to use the xls to produce a graph but in this simple example am using html as the output. I am using Altova XMLSpy to test the xls and see the resulting html document.

Here is the simple xls code I have written to group and display the data in HTML :

<?xml version="1.0" encoding="UTF-8"?>
<xsl:stylesheet version="2.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform">
<xsl:template match="/">
  <html>
  <body>
  <table border="1">
    <tr>
      <th>Year</th>
      <th>Amount</th>
    </tr>
    <xsl:for-each-group select="sales/sale" group-by="year">
      <xsl:sort select="year"/>
      <tr>
        <td><xsl:value-of select="year"/></td>
        <td><xsl:value-of select="sum(current-group()/amount)"/></td>
      </tr>
    </xsl:for-each-group>
  </table>
  </body>
  </html>
</xsl:template>

</xsl:stylesheet>

When I run this using Altova XMLSpy against the xml file I get the below in a browser window. A small table with 3 rows, a header with titles and then 2 rows of data, one for 2012, the other 2010. I can't use images yet as a new user to stackoverflow so here is the source html code that can be viewed in a browser.

<html>
    <body>
        <table border="1">
            <tr>
                <th>Year</th>
                <th>Amount</th>
            </tr>
            <tr>
                <td>2010</td>
                <td>120</td>
            </tr>
            <tr>
                <td>2012</td>
                <td>380</td>
            </tr>
        </table>
    </body>
</html>

Can I create a row for the Year 2011 with an amount of 0 using xls so that my output shows data for the range of years 2010,2011 and 2012?

Please note I cannot hard code Years in the xls, I have to derive the ones that are missing.

Ordinarily I would ensure the source xml file had all the data required but in this instance it is not possible.

Appreciate any help.

Kind Regards

Cel

Upvotes: 2

Views: 296

Answers (2)

Martin Honnen
Martin Honnen

Reputation: 167706

Here is a solution:

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

<xsl:output method="html" indent="yes"/>

<xsl:key name="sale-by-year" match="sale" use="xs:integer(year)"/>

<xsl:template match="/">
  <html>
  <body>
  <table border="1">
    <tr>
      <th>Year</th>
      <th>Amount</th>
    </tr>
    <xsl:variable name="doc" select="/"/>
    <xsl:for-each select="min(sales/sale/year/xs:integer(.)) to max(sales/sale/year/xs:integer(.))">
      <xsl:variable name="sales-of-year" select="key('sale-by-year', ., $doc)"/>
      <tr>
        <td>
          <xsl:value-of select="."/>
        </td>
        <td>
          <!--  <xsl:value-of select="if ($sales-of-year) then sum($sales-of-year/amount) else 0"/> -->
          <xsl:value-of select="sum($sales-of-year/amount)"/>
        </td>
       </tr>
     </xsl:for-each>

  </table>
  </body>
  </html>
</xsl:template>

</xsl:stylesheet>

Upvotes: 3

Dimitre Novatchev
Dimitre Novatchev

Reputation: 243529

I. Slightly shorter XSLT 2.0:

<xsl:stylesheet version="2.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform"
 xmlns:xs="http://www.w3.org/2001/XMLSchema" exclude-result-prefixes="xs">
 <xsl:output omit-xml-declaration="yes" indent="yes"/>
 <xsl:key name="kSalesByYear" match="sale" use="year"/>
 <xsl:variable name="vDoc" select="/"/>

 <xsl:template match="/">
  <html>
    <body>
          <table border="1">
            <tr>
              <th>Year</th>
              <th>Amount</th>
            </tr>
        <xsl:for-each select=
         "min(/*/*/year/xs:integer(.)) to max(/*/*/year/xs:integer(.))">
         <tr>
           <td><xsl:sequence select="."/></td>
           <td><xsl:sequence select=
            "sum(key('kSalesByYear',string(.),$vDoc)/amount)"/></td>
         </tr>
        </xsl:for-each>
      </table>
    </body>
   </html>
 </xsl:template>
</xsl:stylesheet>

II. XSLT 1.0 solution:

<xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform">
 <xsl:output omit-xml-declaration="yes" indent="yes"/>
 <xsl:key name="kSalesByYear" match="sale" use="year"/>
 <xsl:variable name="vDoc" select="/"/>

 <xsl:variable name="vMinMax">
  <xsl:for-each select="/*/*/year">
   <xsl:sort/>
   <xsl:if test="position()=1">
    <xsl:value-of select="."/>
   </xsl:if>
   <xsl:if test="position()=last()">
    <xsl:value-of select="concat('|',.)"/>
   </xsl:if>
  </xsl:for-each>
 </xsl:variable>

 <xsl:variable name="vMin" select="substring-before($vMinMax, '|')"/>
 <xsl:variable name="vMax" select="substring-after($vMinMax, '|')"/>

 <xsl:template match="/">
  <html>
    <body>
          <table border="1">
            <tr>
              <th>Year</th>
              <th>Amount</th>
            </tr>
        <xsl:for-each select=
         "(document('')//node()|document('')//@*|document('')//namespace::*)
           [not(position() > $vMax - $vMin +1)]
         ">
         <xsl:variable name="vYear" select="$vMin + position() -1"/>

         <xsl:for-each select="$vDoc">
             <tr>
               <td><xsl:value-of select="$vYear"/></td>
               <td><xsl:value-of select=
                "sum(key('kSalesByYear',$vYear)/amount)"/></td>
             </tr>
         </xsl:for-each>
        </xsl:for-each>
      </table>
    </body>
   </html>
 </xsl:template>
</xsl:stylesheet>

When any of the two transformationsabove are applied on the provided XML document:

<sales>
    <sale>
        <year>2012</year>
        <amount>200</amount>
    </sale>
    <sale>
        <year>2012</year>
        <amount>180</amount>
    </sale>
    <sale>
        <year>2010</year>
        <amount>120</amount>
    </sale>
</sales>

the wanted, correct result is produced:

<html>
   <body>
      <table border="1">
         <tr>
            <th>Year</th>
            <th>Amount</th>
         </tr>
         <tr>
            <td>2010</td>
            <td>120</td>
         </tr>
         <tr>
            <td>2011</td>
            <td>0</td>
         </tr>
         <tr>
            <td>2012</td>
            <td>380</td>
         </tr>
      </table>
   </body>
</html>

Upvotes: 0

Related Questions