CODEWITHSUNDEEP
xmlxsltgrouping
Jacob
Jacob

Reputation: 1

XML grouping Muenchian method problems.

trying to do grouping in XSLT 1.0 version, but something doesnt work, and I dont see what exactly is wrong. I need group by VatCode and sum all sums by same VatCode in Invoice. Any help? adding XML(added by 2 sheets but they are in one file as 2 different lines) and XSL. Maybe someone can help me, and take a look into XML and XSL?

XML:

    <E_Invoice xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
<Header>
    <Test>YES</Test>
    <Date>2018-03-05</Date>
    <FileId>dst05.03.18 12:02:34</FileId>
    <Version>1.2</Version>
    <ReceiverId>111529447</ReceiverId>
</Header>
    <Invoice regNumber="111529447" invoiceId="63">
    <InvoiceItem>
        <InvoiceItemGroup>
            <ItemEntry>
                <Accounting>
                    <Description>KR0002202</Description>
                    <JournalEntry>
                        <GeneralLedger>61181</GeneralLedger>
                        <CostObjective>
                            <Description/>
                            <VatCode>08</VatCode>
                            <AccountingDate/>
                            <PurchaseLedger/>
                            <DIM03>290</DIM03>
                            <DIM02>03TPRO</DIM02>
                        </CostObjective>
                        <Sum>9953.4000</Sum>
                        <VatSum>2090.2100</VatSum>
                        <VatRate>21.0000</VatRate>
                    </JournalEntry>
                </Accounting>
                <Description>KR0002202</Description>
                <ItemSum>9953.4000</ItemSum>
                <VAT>
                    <SumBeforeVAT>9953.4000</SumBeforeVAT>
                    <VATRate>21.0000</VATRate>
                    <VATSum>2090.2100</VATSum>
                    <Currency>EUR</Currency>
                </VAT>
                <ItemTotal>12043.6100</ItemTotal>
            </ItemEntry>
        </InvoiceItemGroup>
    </InvoiceItem>
</Invoice>
<Invoice regNumber="111529447" invoiceId="65">
    <InvoiceItem>
        <InvoiceItemGroup>
            <ItemEntry>
                <Accounting>
                    <Description>LP2018000033</Description>
                    <JournalEntry>
                        <GeneralLedger>10090</GeneralLedger>
                        <CostObjective>
                            <Description/>
                            <VatCode>08</VatCode>
                            <AccountingDate/>
                            <PurchaseLedger/>
                            <DIM02>03TPRO</DIM02>
                            <DIM03>283</DIM03>
                            <DIM04>COM</DIM04>
                            <DIM01>01</DIM01>
                        </CostObjective>
                        <Sum>466.9400</Sum>
                        <VatSum>98.0600</VatSum>
                        <VatRate>21.0000</VatRate>
                    </JournalEntry>
                </Accounting>
                <Description>LP2018000033</Description>
                <ItemSum>466.9400</ItemSum>
                <VAT>
                    <SumBeforeVAT>466.9400</SumBeforeVAT>
                    <VATRate>21.0000</VATRate>
                    <VATSum>98.0600</VATSum>
                    <Currency>EUR</Currency>
                </VAT>
                <ItemTotal>565.0000</ItemTotal>
            </ItemEntry>
            <ItemEntry>
                <Accounting>
                    <Description>21 %</Description>
                    <JournalEntry>
                        <GeneralLedger>10000</GeneralLedger>
                        <CostObjective>
                            <Description/>
                            <VatCode>02</VatCode>
                            <AccountingDate/>
                            <PurchaseLedger/>
                            <DIM02>ADMHR</DIM02>
                            <DIM03>289</DIM03>
                            <DIM04>MET</DIM04>
                            <DIM01>02</DIM01>
                        </CostObjective>
                        <Sum>1210.0000</Sum>
                        <VatSum>0.0000</VatSum>
                        <VatRate>0.0000</VatRate>
                    </JournalEntry>
                </Accounting>
                <Description>21 %</Description>
                <ItemSum>1210.0000</ItemSum>
                <VAT>
                    <SumBeforeVAT>1210.0000</SumBeforeVAT>
                    <VATRate>0.0000</VATRate>
                    <VATSum>0.0000</VATSum>
                    <Currency>EUR</Currency>
                </VAT>
                <ItemTotal>1210.0000</ItemTotal>
            </ItemEntry>
        </InvoiceItemGroup>
    </InvoiceItem>

XSL: -->

<xsl:key name="sum-by-vatcode" match="ItemEntry"
use="concat(generate-id(..), Accounting/JournalEntry/CostObjective/VatCode)"/>

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

    <xsl:template match="Invoice">
    <agency>
        <xsl:for-each select=
        "ItemEntry[generate-id()=generate-id(key('sum-by-vatcode', concat(generate-id(..), Accounting/JournalEntry/CostObjective/VatCode))[1])]">
        <xsl:variable name="vkeyGroup" select=
        "key('sum-by-vatcode', concat(generate-id(..), Accounting/JournalEntry/CostObjective/VatCode))"/>

        <ItemEntry>
        <xsl:copy-of select="*[starts-with(name(), 'key')]"/>
        <VATSum>
        <xsl:value-of select="sum($vkeyGroup/VAT/VATSum)"/>
        </VATSum>
        <Sum>
        <xsl:value-of select="sum($vkeyGroup/VAT/SumBeforeVAT)"/>
        </Sum>
        </ItemEntry>
        </xsl:for-each>
    </agency>
    </xsl:template>

Upvotes: 0

Views: 35

Answers (1)

Martin Honnen
Martin Honnen

Reputation: 167516

Correct the path

<xsl:for-each select=
        "ItemEntry[generate-id()=generate-id(key('sum-by-vatcode', concat(generate-id(..), Accounting/JournalEntry/CostObjective/VatCode))[1])]">

to

<xsl:for-each select=
        ".//ItemEntry[generate-id()=generate-id(key('sum-by-vatcode', concat(generate-id(..), Accounting/JournalEntry/CostObjective/VatCode))[1])]">

to at least have a chance to select the ItemEntry descendants of your template's Invoice context node.

With that change at https://xsltfiddle.liberty-development.net/bFDb2BR I get the output

<E_Invoice xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
  <Header>
    <Test>YES</Test>
    <Date>2018-03-05</Date>
    <FileId>dst05.03.18 12:02:34</FileId>
    <Version>1.2</Version>
    <ReceiverId>111529447</ReceiverId>
  </Header>
  <agency>
    <ItemEntry>
      <VATSum>2090.21</VATSum>
      <Sum>9953.4</Sum>
    </ItemEntry>
  </agency>
  <agency>
    <ItemEntry>
      <VATSum>98.06</VATSum>
      <Sum>466.94</Sum>
    </ItemEntry>
    <ItemEntry>
      <VATSum>0</VATSum>
      <Sum>1210</Sum>
    </ItemEntry>
  </agency>
</E_Invoice>

for the code

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

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

<xsl:key name="sum-by-vatcode" match="ItemEntry"
use="concat(generate-id(..), Accounting/JournalEntry/CostObjective/VatCode)"/>

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

    <xsl:template match="Invoice">
    <agency>
        <xsl:for-each select=
        ".//ItemEntry[generate-id()=generate-id(key('sum-by-vatcode', concat(generate-id(..), Accounting/JournalEntry/CostObjective/VatCode))[1])]">
        <xsl:variable name="vkeyGroup" select=
        "key('sum-by-vatcode', concat(generate-id(..), Accounting/JournalEntry/CostObjective/VatCode))"/>

        <ItemEntry>
        <xsl:copy-of select="*[starts-with(name(), 'key')]"/>
        <VATSum>
        <xsl:value-of select="sum($vkeyGroup/VAT/VATSum)"/>
        </VATSum>
        <Sum>
        <xsl:value-of select="sum($vkeyGroup/VAT/SumBeforeVAT)"/>
        </Sum>
        </ItemEntry>
        </xsl:for-each>
    </agency>
    </xsl:template>

</xsl:stylesheet>

Upvotes: 1

Related Questions