XSL 1.0 group by and sum SalesAmount and related /

Question

I have an invoice xml where I need to perform a grouping / sum on itemid's For instance for ItemId 444 I need a lineamount of 100 + 25 = 125 and ItemId 555 = 200 + 15. Lot's of examples out there how to do this you're about to say but I have another requirement. I also need to do the same grouping and sum for my TaxTrans\TaxAmounts and TaxTrans\TaxBaseAmounts based on the joining InventTransId field (AABB / BBCC) between CustInvoiceTrans and TaxTrans.

Before:

  <CustInvoiceJour class="entity">
          <CustInvoiceTrans class="entity">
            <InventTransId>AABB</InventTransId>
            <InvoiceId>SI100</InvoiceId>
            <ItemId>444</ItemId>
            <LineAmount>100</LineAmount>
          </CustInvoiceTrans>
          <CustInvoiceTrans class="entity">
            <InventTransId>BBCC</InventTransId>
            <InvoiceId>SI100</InvoiceId>
            <ItemId>444</ItemId>
            <LineAmount>25</LineAmount>
          </CustInvoiceTrans>
          <CustInvoiceTrans class="entity">
            <InventTransId>CCDD</InventTransId>
            <InvoiceId>SI100</InvoiceId>
            <ItemId>555</ItemId>
            <LineAmount>200</LineAmount>
          </CustInvoiceTrans>
          <CustInvoiceTrans class="entity">
            <InventTransId>DDEE</InventTransId>
            <InvoiceId>SI100</InvoiceId>
            <ItemId>555</ItemId>
            <LineAmount>15</LineAmount>
          </CustInvoiceTrans>         
          <CustInvoiceTrans class="entity">
            <InventTransId>EEFF</InventTransId>
            <InvoiceId>SI100</InvoiceId>
            <ItemId>12345</ItemId>
            <LineAmount>40</LineAmount>
          </CustInvoiceTrans>
          <TaxTrans class="entity">
            <InventTransId>AABB</InventTransId>
            <TaxAmount>-21</TaxAmount>
            <TaxBaseAmount>-100</TaxBaseAmount>
            <TaxValue>21.00</TaxValue>
          </TaxTrans>
          <TaxTrans class="entity">
            <InventTransId>BBCC</InventTransId>
            <TaxAmount>-5.25</TaxAmount>
            <TaxBaseAmount>-25</TaxBaseAmount>
            <TaxValue>21.00</TaxValue>
          </TaxTrans>
          <TaxTrans class="entity">
            <InventTransId>CCDD</InventTransId>
            <TaxAmount>-42</TaxAmount>
            <TaxBaseAmount>-200</TaxBaseAmount>
            <TaxValue>21.00</TaxValue>
          </TaxTrans>
          <TaxTrans class="entity">
            <InventTransId>DDEE</InventTransId>
            <TaxAmount>-3.15</TaxAmount>
            <TaxBaseAmount>-15</TaxBaseAmount>
            <TaxValue>15</TaxValue>
          </TaxTrans>
          <TaxTrans class="entity">
            <InventTransId>EEFF</InventTransId>
            <TaxAmount>-8.40</TaxAmount>
            <TaxBaseAmount>-40</TaxBaseAmount>
            <TaxValue>15</TaxValue>
          </TaxTrans>         
</CustInvoiceJour>

Required output:

<CustInvoiceJour class="entity">
    <CustInvoiceTrans class="entity">
        <InventTransId>AABB</InventTransId>
        <InvoiceId>SI100</InvoiceId>
        <ItemId>444</ItemId>
        <LineAmount>125</LineAmount>
    </CustInvoiceTrans>
    <CustInvoiceTrans class="entity">
        <InventTransId>CCDD</InventTransId>
        <InvoiceId>SI100</InvoiceId>
        <ItemId>555</ItemId>
        <LineAmount>215</LineAmount>
    </CustInvoiceTrans>
    <CustInvoiceTrans class="entity">
        <InventTransId>EEFF</InventTransId>
        <InvoiceId>SI100</InvoiceId>
        <ItemId>12345</ItemId>
        <LineAmount>40</LineAmount>
    </CustInvoiceTrans>
    <TaxTrans class="entity">
        <InventTransId>AABB</InventTransId>
        <TaxAmount>-26.25</TaxAmount>
        <TaxBaseAmount>-125</TaxBaseAmount>
        <TaxValue>21.00</TaxValue>
    </TaxTrans>
    <TaxTrans class="entity">
        <InventTransId>CCDD</InventTransId>
        <TaxAmount>-45.15</TaxAmount>
        <TaxBaseAmount>-215</TaxBaseAmount>
        <TaxValue>21.00</TaxValue>
    </TaxTrans>
    <TaxTrans class="entity">
        <InventTransId>EEFF</InventTransId>
        <TaxAmount>-8.40</TaxAmount>
        <TaxBaseAmount>-40</TaxBaseAmount>
        <TaxValue>15</TaxValue>
    </TaxTrans>       
</CustInvoiceJour>

I don't know if this is even possible. Let alone where to start?

Mike

Answer 1

You can group the CustInvoiceTrans elements twice, once to group and sum up their components, the second time to create the sum of referenced TaxTrans elements which you simply find with a key <xsl:key name="RefTaxTrans" match="TaxTrans" use="InventTransId"/>; the following shows how to create the grouped TaxTrans and computes the sum for the TaxAmount, you just need to copy the other elements respectively sum the other element inside that template and the TaxTrans result element:

<xsl:stylesheet
    xmlns:xsl="http://www.w3.org/1999/XSL/Transform"
    version="1.0">

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

  <xsl:key name="CustInvoiceTrans" match="CustInvoiceTrans" use="ItemId"/>
  <xsl:key name="RefTaxTrans" match="TaxTrans" use="InventTransId"/>

  <xsl:template match="@* | node()">
    <xsl:copy>
      <xsl:apply-templates select="@* | node()"/>
    </xsl:copy>
  </xsl:template>

  <xsl:template match="CustInvoiceJour">
      <xsl:copy>
          <xsl:apply-templates select="@*"/>
          <xsl:apply-templates select="CustInvoiceTrans[generate-id() = generate-id(key('CustInvoiceTrans', ItemId)[1])]" mode="tax"/>
      </xsl:copy>
  </xsl:template>

  <xsl:template match="CustInvoiceTrans" mode="tax">
      <TaxTrans>
          <xsl:variable name="referenced-tax" select="key('RefTaxTrans', key('CustInvoiceTrans', ItemId)/InventTransId)"/>
          <TaxAmount>
              <xsl:value-of select="sum($referenced-tax/TaxAmount)"/>
          </TaxAmount>
      </TaxTrans>
  </xsl:template>

</xsl:stylesheet>

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

You can then add the normal grouping for the CustInvoiceTrans elements with another apply-templates in the default mode before the <xsl:apply-templates select="CustInvoiceTrans[generate-id() = generate-id(key('CustInvoiceTrans', ItemId)[1])]" mode="tax"/> line. Or it might be easier to store the first item of each group and then apply-templates twice, once in the default, unnamed mode, the second time in the mode for the referenced items:

<?xml version="1.0" encoding="UTF-8"?>
<xsl:stylesheet
    xmlns:xsl="http://www.w3.org/1999/XSL/Transform"
    version="1.0">

  <xsl:output method="xml" indent="yes" />
  <xsl:strip-space elements="*"/>

  <xsl:key name="CustInvoiceTrans" match="CustInvoiceTrans" use="ItemId"/>
  <xsl:key name="RefTaxTrans" match="TaxTrans" use="InventTransId"/>

  <xsl:template match="@* | node()">
    <xsl:copy>
      <xsl:apply-templates select="@* | node()"/>
    </xsl:copy>
  </xsl:template>

  <xsl:template match="CustInvoiceJour">
      <xsl:copy>
          <xsl:apply-templates select="@*"/>
          <xsl:variable name="group-heads"
             select="CustInvoiceTrans[generate-id() = generate-id(key('CustInvoiceTrans', ItemId)[1])]"/>
          <xsl:apply-templates select="$group-heads"/>
          <xsl:apply-templates select="$group-heads" mode="tax"/>
      </xsl:copy>
  </xsl:template>

  <xsl:template match="CustInvoiceTrans/LineAmount">
      <xsl:copy>
          <xsl:value-of select="sum(key('CustInvoiceTrans', ../ItemId)/LineAmount)"/>
      </xsl:copy>
  </xsl:template>

  <xsl:template match="CustInvoiceTrans" mode="tax">
      <TaxTrans class="entity">
          <xsl:variable name="referenced-tax" select="key('RefTaxTrans', key('CustInvoiceTrans', ItemId)/InventTransId)"/>
          <xsl:copy-of select="$referenced-tax[1]/InventTransId"/>
          <TaxAmount>
              <xsl:value-of select="sum($referenced-tax/TaxAmount)"/>
          </TaxAmount>
          <TaxBaseAmount>
              <xsl:value-of select="sum($referenced-tax/TaxBaseAmount)"/>
          </TaxBaseAmount>
          <xsl:copy-of select="$referenced-tax[1]/TaxValue"/>
      </TaxTrans>
  </xsl:template>

</xsl:stylesheet>

https://xsltfiddle.liberty-development.net/jyH9rMG/1