Picking the last in a sequence from a for-each-group with nested if

Question

I've hacked my way around this issue, but I'd like to implement a better code. I'm trying to determine the last date where a non-zero financial event occurs. Note that this is a stripped and altered XML for brevity so excuse any formatting oddities. The actual XML I'm using has Change amounts from 1/1/2018 through 12/31/2029 with everything from 4/1/2018 to 12/31/2029 totaling to 0.

<Products>
  <Cost>
    <Company name="A"/>
    <Financials>
       <Change amount="-10000">
          <Date date="1/1/2018" dateindays="42734">
          <Type name="open">
       <Change>
       <Change amount="4500">
          <Date date="2/1/2018" dateindays="42765">
          <Type name="debit">
       </Change>
       <Change amount="4500">
          <Date date="3/1/2018" dateindays="42793">
          <Type name="debit">
       </Change>
       <Change amount="4500">
          <Date date="4/1/2018" dateindays="42824">
          <Type name="debit">
       </Change>
       <Change amount="-2000">
          <Date date="4/1/2018" dateindays="42824">
          <Type name="debit">
       <Change>
       <Change amount="-2500">
          <Date date="4/1/2018" dateindays="42824">
          <Type name="debit">
    </Financials>
  </Cost>
</Products>

In the above snippet I'd want the 3/1/2018 date, since the 4/1/2018 date totals to a 0 debit.

Using XSLT 2.0, I've used the following code to exclude the date of 4/1/2018, but I haven't figured a way to only return the 3/1/2018 date. Every thing I've tried has returned the 4/1/2018 as the last date in the series.

<xsl:for-each-group select="Products/Cost[Company/@name = 
    $Company]/Financials/Change[Type/@name = 'Debit']/Date" group-
    by="@dateindays">
  <xsl:sort select="@dateindays" data-type="number" order="descending"/>
  <xsl:variable name="actDate" select="@dateindays"/>
  <xsl:if test="sum(/Products/Cost[Company/@name = 
    $Company]/Financials/Change[Type/@name = 'Debit' and Date/@dateindays = 
    $actDate]/@amount) != 0">
      <xsl:value-of select="@date"/>
  </xsl:if>
</xsl:for-each-group>

As I said, I did some hacking (hidden cells in Excel), but I really want to handle it all in code as it appears this date might become required as part of later filtering within code.

Did you look here: https://stackoverflow.com/questions/1738832/xsl-for-each-how-to-detect-last-node ? — AntonH, Mar 16 '18 at 19:52
I don't see how that is helpful. The crux of my issue is I need to exclude April from the data set and instead return March. The If statement is there to get the required April exclusion, but when I make that If into a for-each I'm getting all dates in my returned data set instead of all dates except April. In reality the node I want is randomly placed somewhere within 800+ Change nodes. Perhaps I just am missing what that thread is telling me. — Jason H, Mar 16 '18 at 20:11
I think I misread what you were asking for. I though you wanted to skip the last element, not the last "element-value". — AntonH, Mar 16 '18 at 20:16
After careful reading I have come to the conclusion that your dates must be in MM/DD/YYYY format. It would have been useful to tell us that. It would be even more useful to use a more standard format. — Michael Kay, Mar 16 '18 at 23:38

score 3 · Accepted Answer · answered Mar 16 '18 at 20:34

3

I think you can use the sum of the current-group for the check and then you need to output those elements in a variable and then take the first item in that variable value:

  <xsl:variable name="non-zero" as="element(Date)*">
      <xsl:for-each-group select="Products/Cost[Company/@name = 'A']/Financials/Change[Type/@name = 'debit']" group-by="Date/@dateindays">
          <xsl:sort select="current-grouping-key()" data-type="number" order="descending"/>
          <xsl:variable name="actDate" select="@dateindays"/>
          <xsl:if test="sum(current-group()/@amount) != 0">
              <Date>{Date/@date}</Date>
          </xsl:if>
      </xsl:for-each-group>
  </xsl:variable>
  <xsl:value-of select="$non-zero[1]"/>

https://xsltfiddle.liberty-development.net/pPgCcoC

The <Date>{Date/@date}</Date> is XSLT 3, in XSLT 2 you need <Date><xsl:value-of select="Date/@date"/></Date>.

answered Mar 16 '18 at 20:34

Martin Honnen

160,499
6
90
110

Well, it appears I have some odd data I didn't see before (not really surprising with a 6Mb XML I guess). I'm getting the fatal error: "A sequence of more than one item is not allowed as the value of variable $non-zero (, , ...)". I'm also seeing the warning message: "The only value that can pass type-checking is an empty sequence. Required item type of value of variable $non-zero is element(Q{}Date); supplied value has item type element(Q{urn:schemas-microsoft-com:office:spreadsheet}Date, Q{http://www.w3.org/2001/XMLSchema}untyped)" – Jason H Mar 16 '18 at 21:14
If your XSLT puts result elements into a namespace you need to adjust the variable code to `` to ensure the type I declared as `element(Date)*` is correct. It however sounds as if you don't have the asterisk in your code so make sure the variable is declared as a sequence of zero or more items with `element(Date)*` and not as a single element (as your error message suggests). – Martin Honnen Mar 16 '18 at 21:26
That did it! Thank you! – Jason H Mar 16 '18 at 21:44

score 0 · Answer 2 · answered Mar 16 '18 at 20:50

Here's another option that is similar to Martin's. I like Martin's better (+1), but since I'd already written it I'll go ahead and add it.

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="#all">
  <xsl:output indent="yes"/>
  <xsl:strip-space elements="*"/>

  <xsl:param name="company" select="'A'"/>

  <xsl:key name="changes" match="Change" use="Date/@dateindays"/>

  <xsl:template match="/">
    <xsl:apply-templates select="Products/Cost[Company/@name = $company]/Financials"/>
  </xsl:template>

  <xsl:template match="Financials">
    <xsl:variable name="non-zero-days" as="xs:float*">
      <xsl:for-each-group select="Change" group-by="Date/@dateindays">
        <xsl:if test="sum(current-group()/@amount) != 0">          
          <xsl:sequence select="data(Date/@dateindays)"/>
        </xsl:if>
      </xsl:for-each-group>      
    </xsl:variable>
    <results>
      <xsl:value-of select="key('changes',string(max($non-zero-days)))[1]/Date/@date"/>
    </results>
  </xsl:template>

</xsl:stylesheet>

Output

<results>3/1/2018</results>

Working fiddle: https://xsltfiddle.liberty-development.net/3Nqn5Yn/1

score 0 · Answer 3 · answered Mar 16 '18 at 23:49

I would go for a two-phase approach: group the data first, then do what you need to with the groups.

In the first phase, you transform

   <Change amount="4500">
      <Date date="4/1/2018" dateindays="42824">
      <Type name="debit">
   </Change>
   <Change amount="-2000">
      <Date date="4/1/2018" dateindays="42824">
      <Type name="debit">
   </Change>
   <Change amount="-2500">
      <Date date="4/1/2018" dateindays="42824">
      <Type name="debit">
   </Change>

to

<Date date="4/1/2018" dateindays="42824">
  <Change amount="4500">
          <Type name="debit">
  </Change>
  <Change amount="-2000">
          <Type name="debit">
  </Change>
  <Change amount="-2500">
          <Type name="debit">
  </Change>
</Date>

(which is a trivial use of xsl:for-each-group)

In the second phase, you apply filtering, for example Date[sum(Change/@amount)!=0)][last()]

Picking the last in a sequence from a for-each-group with nested if

3 Answers3