XSLT sum of values based on data in two different tables

Question

XML:

<Budget>
    <Table1>
        <AllocID>1</AllocID>
        <Amount>1000</Amount>
    </Table1>
    <Table1>
        <AllocID>2</AllocID>
        <Amount>2000</Amount>
    </Table1>
    <Table1>
        <AllocID>3</AllocID>
        <Amount>3000</Amount>
    </Table1>
    <Table2>
        <AllocID>1</AllocID>
        <Split>100</Split>
    </Table2>
    <Table2>
        <AllocID>2</AllocID>
        <Split>100</Split>
    </Table2>
</Budget>

I am displaying the amounts in a table, but only if a "Split" value exists in Table2.

<xsl:for-each select="Budget/Table1">
    <xsl:for-each select="//Budget/Table2[AllocID = current()/AllocID]">
        <xsl:value-of select="Amount"/>
    </xsl:for-each>
</xsl:for-each>
//Total for the records here.

I need to get the sum of Amounts from Table1, but only if a value for the AllocID exists in Table2. So in this example, I only want the sum of Amount for AllocIDs 1 & 2. How would I do this in xslt without modifying the given datasets?

Answer 1

Knowing nothing else about your input data (I have no idea why you chose not to use actual XML), I'm going to have to to guess a little:

<Budget>
  <Table1>
    <AllocID>1000</AllocID>
    <AllocID>2000</AllocID>
    <AllocID>3000</AllocID>
  </Table1>
  <Table2>
    <AllocID>1000</AllocID>
    <AllocID>2000</AllocID>
  </Table2>
</Budget>

Easiest is the XPath sum() function along with the right XPath expression:

<!-- this will return 3000 for the above input -->
<xsl:template match="/" >
  <xsl:value-of select="
    sum(Budget/Table1/AllocID[. = //Budget/Table2/AllocID])
  " />
</xsl:template>

Running sums can also be calculated with a recursive function, like this:

<!-- this will also return 3000 for the above input -->
<xsl:template match="/" >
  <xsl:call-template name="total">
    <xsl:with-param name="nodes" select="
      Budget/Table1/AllocID[. = //Budget/Table2/AllocID]
    " />
  </xsl:call-template>
</xsl:template>

<xsl:template name="total">
  <xsl:param name="nodes" />

  <xsl:choose>
    <!-- either there is something to calculate... -->
    <xsl:when test="number($nodes[1])">
      <xsl:variable name="subtotal">
        <xsl:call-template name="total">
          <xsl:with-param name="nodes" select="$nodes[position() &gt; 1]" />
        </xsl:call-template>
      </xsl:variable>
      <xsl:value-of select="number($nodes[1]) + $subtotal" /> 
    </xsl:when>
    <!-- ...or we assume 0 -->
    <xsl:otherwise>
      <xsl:value-of select="0" /> 
    </xsl:otherwise>
  </xsl:choose>

</xsl:template>

This is slower, but allows for greater flexibility in the calculation process. You can replace all the non-numeric values with 0, for example. Or use different values of the given nodes according to your own rules.