XSLT 1.0 - Sum column based on conditions from 2 nodes

Question

I have 2 data sources (Request and Actual). Below is a sample of my xml:

<dsQuery Response>
  <Request>
    <Rows>
      <Row>
        <TravelDate>2013-10-05T05:00:00Z</TravelDate>
        <ID>1</ID>
       <Cost>1000</Cost>
     </Row>
     <Row>
        <TravelDate>2013-12-31T05:00:00Z</TravelDate>
        <ID>2</ID
        <Cost>2500</Cost>
     </Row>
     <Row>
      <TravelDate>2014-01-13T06:00:00Z</TravelDate>
      <ID>3</ID>
      <Cost>1300</Cost>
     </Row>
   <Row>
      <TravelDate>2014-02-01T06:00:00Z</TravelDate>
      <ID>4</ID>
      <Cost>2300</Cost>
    </Row>
    <Row>
      <TravelDate>2014-08-01T06:00:00Z</TravelDate>
      <ID>5</ID>
      <Cost>2000</Cost>
    </Row>
    </Rows>
  </Request>
  <Actual>
    <Rows>
      <Row>
        <ID>10</ID>
        <FormID>2</FormID>
        <CheckDate>2014-01-01T12:00:00Z</CheckDate>
      </Row>
      <Row>
        <ID>11</ID>
        <FormID>3</FormID>
        <CheckDate>2014-01-31T12:00:00Z</CheckDate>
    </Row>
    <Row>
      <ID>12</ID>
      <FormID>4</FormID>
      <CheckDate>2014-02-15T12:00:00Z</CheckDate>
    </Row>
  </Rows>
</Actual>
</dsQuery Response>

I need to sum the Cost column if the year for TravelDate=This year OR if the year of the CheckDate = This Year.

In the scenario above Request ID 2-5 meet the criteria. The total should be 8100. I have tried several ways to get the sum and none are working. Any help is appreciated.

Please fix your input XML example: `` is not a valid element name, and you have an open tag at line 11. — michael.hor257k, Apr 07 '14 at 19:09

Rubens Farias · Answer 1 · 2014-04-07T18:39:04.620

You could use a xsl:variable to hold your filtered Row elements and sum them:

<?xml version="1.0" encoding="ISO-8859-1"?>
<xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform">
    <xsl:output method="xml"/>
    <xsl:template match="/dsQueryResponse">

        <xsl:variable name="ThisYear" select="2014" />
        <xsl:variable name="Rows">
            <xsl:for-each select="//Cost">
                <xsl:variable name="ID" select="../ID" />
                <xsl:if test="
                    starts-with(../TravelDate, $ThisYear) or
                    (
                        /*/Actual/*/*[FormID = $ID and 
                        starts-with(CheckDate, $ThisYear) ]
                    )">
                    <xsl:copy-of select=".." />
                </xsl:if>
            </xsl:for-each>
        </xsl:variable>

        <xsl:value-of select="sum($Rows//Cost)" />

    </xsl:template>
</xsl:stylesheet>

You can't sum a result tree fragment in XSLT 1.0. – michael.hor257k Apr 07 '14 at 19:13 — michael.hor257k, Apr 07 '14 at 19:13

score 0 · Answer 2 · answered Apr 07 '14 at 18:40

0

This will output <sum>5600</sum>

<xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform"> 
   <xsl:param name="year" select="string('2014')"/>

   <xsl:template match="/"> 
       <sum>
         <xsl:value-of select="sum(//Request/Rows/Row[substring-before(TravelDate/text(),'-')=$year]/Cost)  
                     + sum(//Actual/Rows/Row[substring-before(CheckDate,'-')=$year]/Cost)"/>
       </sum>

   </xsl:template>
</xsl:stylesheet>

answered Apr 07 '14 at 18:40

deanosaur

621
3
5

`Actual` rows doesn't contains `Cost` elements – Rubens Farias Apr 07 '14 at 18:41
@RubensFarias true in the sample xml, but I read this `I need to sum the Cost column if the year for TravelDate=This year OR if the year of the CheckDate = This Year.` to mean that there might be a `Cost` column adjacent to `CheckDate`. – deanosaur Apr 07 '14 at 18:48

score 0 · Answer 3 · answered Apr 07 '14 at 19:06

You are asking for a XSLT 1.0 solution, but there is no "this year" in XSLT 1.0; you will need to use an EXSLT extension function or pass the current date/year as a parameter at runtime.

Other than that. I suggest you use a key to get the data from the "related" actual checkdate:

<?xml version="1.0" encoding="UTF-8"?>
<xsl:stylesheet version="1.0"
xmlns:xsl="http://www.w3.org/1999/XSL/Transform"
xmlns:date="http://exslt.org/dates-and-times"
extension-element-prefixes="date">
<xsl:output method="xml" version="1.0" encoding="UTF-8" indent="yes"/>

<xsl:param name="thisYear" select="substring(date:date-time(), 1, 4)" />

<xsl:key name="check" match="CheckDate" use="../FormID" />

<xsl:template match="/">
    <sum>
        <xsl:value-of select="sum(dsQueryResponse/Request/Rows/Row[substring(TravelDate, 1, 4)=$thisYear or substring(key('check',ID) , 1, 4)=$thisYear]/Cost)"/>
    </sum>  
</xsl:template>

</xsl:stylesheet>

When applied to a corrected example input of:

<dsQueryResponse>
  <Request>
    <Rows>
      <Row>
        <TravelDate>2013-10-05T05:00:00Z</TravelDate>
        <ID>1</ID>
       <Cost>1000</Cost>
     </Row>
     <Row>
        <TravelDate>2013-12-31T05:00:00Z</TravelDate>
        <ID>2</ID>
        <Cost>2500</Cost>
     </Row>
     <Row>
      <TravelDate>2014-01-13T06:00:00Z</TravelDate>
      <ID>3</ID>
      <Cost>1300</Cost>
     </Row>
   <Row>
      <TravelDate>2014-02-01T06:00:00Z</TravelDate>
      <ID>4</ID>
      <Cost>2300</Cost>
    </Row>
    <Row>
      <TravelDate>2014-08-01T06:00:00Z</TravelDate>
      <ID>5</ID>
      <Cost>2000</Cost>
    </Row>
    </Rows>
  </Request>
  <Actual>
    <Rows>
      <Row>
        <ID>10</ID>
        <FormID>2</FormID>
        <CheckDate>2014-01-01T12:00:00Z</CheckDate>
      </Row>
      <Row>
        <ID>11</ID>
        <FormID>3</FormID>
        <CheckDate>2014-01-31T12:00:00Z</CheckDate>
    </Row>
    <Row>
      <ID>12</ID>
      <FormID>4</FormID>
      <CheckDate>2014-02-15T12:00:00Z</CheckDate>
    </Row>
  </Rows>
</Actual>
</dsQueryResponse>

the result is:

<?xml version="1.0" encoding="UTF-8"?>
<sum>8100</sum>

XSLT 1.0 - Sum column based on conditions from 2 nodes

3 Answers3