CODEWITHSUNDEEP
xslt-3.0
Pitchai
Pitchai

Reputation: 3

unable to get sum using distinct loop sum xslt

I am new to xslt. I am creating a transform and need to calculate tax amount based on tax rate. The input file contains different tax rates and tax amount. The requirement is to look for common tax rates, take individual tax amount and sum them all. This creates a single T element.

If there are multiple tax rates, group common tax amounts and total them and form T element.

For example: Input file is: 

<DATA>
    <LIST_G_TRX>
        <G_TRX>
            <LIST_G_HEADER>
                <G_HEADER>
                    <COUNTRYCODE>500</COUNTRYCODE>
                    <INVOICENUMBER>55005</INVOICENUMBER>
                    <CUSTOMER_TRX_ID>182066</CUSTOMER_TRX_ID>
                    <CUSTOMERID>5002013</CUSTOMERID>
                    <CUSTOMERORDERNUMBER>001</CUSTOMERORDERNUMBER>
                    <ORDERDATE>2019-01-09T00:00:00.000+00:00</ORDERDATE>
                    <INVOICEDATE>2019-01-09</INVOICEDATE>
                    <BILLTOCONTACT>XXXLutz Logistik AG</BILLTOCONTACT>
                    <BILLTONAME>XXXLutz Logistik AG</BILLTONAME>
                    <BILLTOADDRESS1>Römerstraße 999</BILLTOADDRESS1>
                    <BILLTOADDRESS2/>
                    <BILLTOADDRESS3/>
                    <BILLTOCITY>Wels</BILLTOCITY>
                    <BILLTOPOSTALCODE>4600</BILLTOPOSTALCODE>
                    <BILLTOCOUNTRYCODE>AT</BILLTOCOUNTRYCODE>
                    <CONTACTPHONENUMBER>11111111</CONTACTPHONENUMBER>
                    <CONTACTPHONEEXT/>
                    <CURRENCY>EUR</CURRENCY>
                    <CUSTOMERTAXID>ATU64784111</CUSTOMERTAXID>
                    <ODTAXID>FN 150931</ODTAXID>
                    <INVOICETYPE>R</INVOICETYPE>
                    <TOTALINVOICEVALUE>124.8</TOTALINVOICEVALUE>
                    <TOTALTAXABLEAMOUNT>104</TOTALTAXABLEAMOUNT>
                    <TOTALTAX>20.80</TOTALTAX>
                    <TOTALMISCCHARGE>0.00</TOTALMISCCHARGE>
                    <TOTALDETAILPRICE/>
                    <PAYERCUSTOMERID>5002013</PAYERCUSTOMERID>
                    <PAYERCONTACT>XXXLutz Logistik AG</PAYERCONTACT>
                    <PAYERNAME>XXXLutz Logistik AG</PAYERNAME>
                    <PAYERADDRESS1>Filialnummer ZL</PAYERADDRESS1>
                    <PAYERADDRESS2>Gewerbestrasse 29</PAYERADDRESS2>
                    <PAYERADDRESS3/>
                    <PAYERCITY>Sattledt</PAYERCITY>
                    <PAYERPOSTALCODE>4642</PAYERPOSTALCODE>
                    <PAYERCOUNTRY>AT</PAYERCOUNTRY>
                    <PAYERPHONENUMBER>22222222</PAYERPHONENUMBER>
                    <PAYERPHONEEXT/>
                    <SOLDTOCUSTOMERID>5002013</SOLDTOCUSTOMERID>
                    <SOLDTONAME>XXXLutz Logistik AG</SOLDTONAME>
                    <SOLDTOADDRESS1>Filialnummer ZL</SOLDTOADDRESS1>
                    <SOLDTOADDRESS2>Gewerbestrasse 29</SOLDTOADDRESS2>
                    <SOLDTOADDRESS3/>
                    <SOLDTOCITY>Sattledt</SOLDTOCITY>
                    <SOLDTOPOSTALCODE>4642</SOLDTOPOSTALCODE>
                    <SOLDTOCOUNTRY>AT</SOLDTOCOUNTRY>
                    <SOLDTOPHONENUMBER>22222222</SOLDTOPHONENUMBER>
                    <SOLDTOPHONEEXT/>
                    <SUPPLIERNAME>Viking Direkt GesmbH</SUPPLIERNAME>
                    <SUPPLIERADDRESS1>Tragweiner Str. 57</SUPPLIERADDRESS1>
                    <SUPPLIERADDRESS2/>
                    <SUPPLIERADDRESS3/>
                    <SUPPLIERCITY>Pregarten</SUPPLIERCITY>
                    <SUPPLIERPOSTALCODE>4230</SUPPLIERPOSTALCODE>
                    <SUPPLIERCOUNTRY>AT</SUPPLIERCOUNTRY>
                    <SUPPLIERPHONENUMBER/>
                    <SUPPLIERPHONEEXT/>
                    <REFERENCEINVOICENUMBER/>
                    <CREDITCARDNUMBER/>
                    <EMAILADDRESS/>
                    <DUEDATE>2019-01-14T00:00:00.000+00:00</DUEDATE>
                    <CREDITNOTEREASON/>
                    <LAST_PRINTED_DATE>2019-01-10T12:47:28.000+00:00</LAST_PRINTED_DATE>
                    <FIRST_PRINT_DATE>2019-01-10T12:47:28.000+00:00</FIRST_PRINT_DATE>
                    <PRINTING_COUNT>1</PRINTING_COUNT>
                    <TRANSACTION_SOURCE>Distributed Order Orchestration</TRANSACTION_SOURCE>
                    <LIST_G_LINES>
                        <G_LINES>
                            <CUSTOMER_TRX_ID>182066</CUSTOMER_TRX_ID>
                            <COUNTRYCODE>500</COUNTRYCODE>
                            <CUSTOMERID>5002013</CUSTOMERID>
                            <CUSTOMERORDERNUMBER>001</CUSTOMERORDERNUMBER>
                            <DELIVERYNOTENUMBER/>
                            <DELIVERYNOTELINENUMBER/>
                            <DELIVERYDATE>2019-01-09T00:00:00.000+00:00</DELIVERYDATE>
                            <INVOICELINENUMBER>2</INVOICELINENUMBER>
                            <ITEMNUMBER>6873291</ITEMNUMBER>
                            <INVOICEDQUANTITY>1</INVOICEDQUANTITY>
                            <PRICE>-20</PRICE>
                            <LINEAMOUNT>-20.00</LINEAMOUNT>
                            <ORDERNUMBER>840</ORDERNUMBER>
                            <UNITOFMEASURE>EA</UNITOFMEASURE>
                            <INVOICENUMBER>55005</INVOICENUMBER>
                            <TAXRATE/>
                            <TAXAMOUNT/>
                            <TAXCODE/>
                            <ITEMDESCRIPTION/>
                            <SHIPTOCONTACT/>
                            <SHIPTONAME>XXXLutz Logistik AG</SHIPTONAME>
                            <SHIPTOADDRESS1>Filialnummer ZL</SHIPTOADDRESS1>
                            <SHIPTOADDRESS2>Gewerbestrasse 29</SHIPTOADDRESS2>
                            <SHIPTOADDRESS3/>
                            <SHIPTOCITY>Sattledt</SHIPTOCITY>
                            <SHIPTOPOSTALCODE>4642</SHIPTOPOSTALCODE>
                            <SHIPTOCOUNTRY>AT</SHIPTOCOUNTRY>
                            <CONTACTPHONENUMBER>22222222</CONTACTPHONENUMBER>
                            <CONTACTPHONEEXT/>
                            <ORDERTYPE>R</ORDERTYPE>
                            <ORDERLINENUMBER>1</ORDERLINENUMBER>
                            <ORDERDATE>2019-01-09</ORDERDATE>
                            <CUSTOMERITEMDESCRIPTION/>
                            <INVOICELINECOMMENT1/>
                            <INVOICELINECOMMENT2/>
                            <TRANSACTIONID/>
                            <CUSTOMERCOSTCENTER/>
                            <CUSTOMERITEMNUMBER/>
                            <CUSTOMERORDERLINENUMBER/>
                            <ORDEREDQTY>1</ORDEREDQTY>
                            <SHIPPEDQTY>1</SHIPPEDQTY>
                        </G_LINES>
                        <G_LINES>
                            <CUSTOMER_TRX_ID>182066</CUSTOMER_TRX_ID>
                            <COUNTRYCODE>500</COUNTRYCODE>
                            <CUSTOMERID>5002013</CUSTOMERID>
                            <CUSTOMERORDERNUMBER>001</CUSTOMERORDERNUMBER>
                            <DELIVERYNOTENUMBER/>
                            <DELIVERYNOTELINENUMBER/>
                            <DELIVERYDATE/>
                            <INVOICELINENUMBER/>
                            <ITEMNUMBER/>
                            <INVOICEDQUANTITY/>
                            <PRICE/>
                            <LINEAMOUNT/>
                            <ORDERNUMBER/>
                            <UNITOFMEASURE/>
                            <INVOICENUMBER>55005</INVOICENUMBER>
                            <TAXRATE>20</TAXRATE>
                            <TAXAMOUNT>24.80</TAXAMOUNT>
                            <TAXCODE/>
                            <ITEMDESCRIPTION>HP 7612 e-All-in-One-GroAformatdrucker</ITEMDESCRIPTION>
                            <SHIPTOCONTACT/>
                            <SHIPTONAME/>
                            <SHIPTOADDRESS1/>
                            <SHIPTOADDRESS2/>
                            <SHIPTOADDRESS3/>
                            <SHIPTOCITY/>
                            <SHIPTOPOSTALCODE/>
                            <SHIPTOCOUNTRY/>
                            <CONTACTPHONENUMBER/>
                            <CONTACTPHONEEXT/>
                            <ORDERTYPE>R</ORDERTYPE>
                            <ORDERLINENUMBER/>
                            <ORDERDATE/>
                            <CUSTOMERITEMDESCRIPTION/>
                            <INVOICELINECOMMENT1/>
                            <INVOICELINECOMMENT2/>
                            <TRANSACTIONID/>
                            <CUSTOMERCOSTCENTER/>
                            <CUSTOMERITEMNUMBER/>
                            <CUSTOMERORDERLINENUMBER/>
                            <ORDEREDQTY/>
                            <SHIPPEDQTY/>
                        </G_LINES>
                        <G_LINES>
                            <CUSTOMER_TRX_ID>182066</CUSTOMER_TRX_ID>
                            <COUNTRYCODE>500</COUNTRYCODE>
                            <CUSTOMERID>5002013</CUSTOMERID>
                            <CUSTOMERORDERNUMBER>001</CUSTOMERORDERNUMBER>
                            <DELIVERYNOTENUMBER/>
                            <DELIVERYNOTELINENUMBER/>
                            <DELIVERYDATE/>
                            <INVOICELINENUMBER/>
                            <ITEMNUMBER/>
                            <INVOICEDQUANTITY/>
                            <PRICE/>
                            <LINEAMOUNT/>
                            <ORDERNUMBER/>
                            <UNITOFMEASURE/>
                            <INVOICENUMBER>55005</INVOICENUMBER>
                            <TAXRATE>20</TAXRATE>
                            <TAXAMOUNT>-4.00</TAXAMOUNT>
                            <TAXCODE/>
                            <ITEMDESCRIPTION>HP</ITEMDESCRIPTION>
                            <SHIPTOCONTACT/>
                            <SHIPTONAME/>
                            <SHIPTOADDRESS1/>
                            <SHIPTOADDRESS2/>
                            <SHIPTOADDRESS3/>
                            <SHIPTOCITY/>
                            <SHIPTOPOSTALCODE/>
                            <SHIPTOCOUNTRY/>
                            <CONTACTPHONENUMBER/>
                            <CONTACTPHONEEXT/>
                            <ORDERTYPE>R</ORDERTYPE>
                            <ORDERLINENUMBER/>
                            <ORDERDATE/>
                            <CUSTOMERITEMDESCRIPTION/>
                            <INVOICELINECOMMENT1/>
                            <INVOICELINECOMMENT2/>
                            <TRANSACTIONID/>
                            <CUSTOMERCOSTCENTER/>
                            <CUSTOMERITEMNUMBER/>
                            <CUSTOMERORDERLINENUMBER/>
                            <ORDEREDQTY/>
                            <SHIPPEDQTY/>
                        </G_LINES>
                        <G_LINES>
                            <CUSTOMER_TRX_ID>182066</CUSTOMER_TRX_ID>
                            <COUNTRYCODE>500</COUNTRYCODE>
                            <CUSTOMERID>5002013</CUSTOMERID>
                            <CUSTOMERORDERNUMBER>001</CUSTOMERORDERNUMBER>
                            <DELIVERYNOTENUMBER/>
                            <DELIVERYNOTELINENUMBER/>
                            <DELIVERYDATE>2019-01-09T00:00:00.000+00:00</DELIVERYDATE>
                            <INVOICELINENUMBER>1</INVOICELINENUMBER>
                            <ITEMNUMBER>6873291</ITEMNUMBER>
                            <INVOICEDQUANTITY>1</INVOICEDQUANTITY>
                            <PRICE>124</PRICE>
                            <LINEAMOUNT>124.00</LINEAMOUNT>
                            <ORDERNUMBER>840</ORDERNUMBER>
                            <UNITOFMEASURE>EA</UNITOFMEASURE>
                            <INVOICENUMBER>55005</INVOICENUMBER>
                            <TAXRATE/>
                            <TAXAMOUNT/>
                            <TAXCODE/>
                            <ITEMDESCRIPTION/>
                            <SHIPTOCONTACT/>
                            <SHIPTONAME>XXXLutz Logistik AG</SHIPTONAME>
                            <SHIPTOADDRESS1>Filialnummer ZL</SHIPTOADDRESS1>
                            <SHIPTOADDRESS2>Gewerbestrasse 29</SHIPTOADDRESS2>
                            <SHIPTOADDRESS3/>
                            <SHIPTOCITY>Sattledt</SHIPTOCITY>
                            <SHIPTOPOSTALCODE>4642</SHIPTOPOSTALCODE>
                            <SHIPTOCOUNTRY>AT</SHIPTOCOUNTRY>
                            <CONTACTPHONENUMBER>22222222</CONTACTPHONENUMBER>
                            <CONTACTPHONEEXT/>
                            <ORDERTYPE>R</ORDERTYPE>
                            <ORDERLINENUMBER>1</ORDERLINENUMBER>
                            <ORDERDATE>2019-01-09</ORDERDATE>
                            <CUSTOMERITEMDESCRIPTION/>
                            <INVOICELINECOMMENT1/>
                            <INVOICELINECOMMENT2/>
                            <TRANSACTIONID/>
                            <CUSTOMERCOSTCENTER/>
                            <CUSTOMERITEMNUMBER/>
                            <CUSTOMERORDERLINENUMBER/>
                            <ORDEREDQTY>1</ORDEREDQTY>
                            <SHIPPEDQTY>1</SHIPPEDQTY>
                        </G_LINES>
                    </LIST_G_LINES>

My output should be similar to this:

<H>
<tax rate 20>
<tax amount 102.60>
</H>
<H>
<tax rate 7>
<tax amount 21.50>
</H>
<H>
<tax rate 19>
<tax amount 13.28>
</H>

My xslt is: 

<xsl:variable name="vTaxRateCount" select="count(distinct-values(LIST_G_LINES/G_LINES/TAXRATE))"/>
                    <xsl:variable name="vDistinct">
                <xsl:for-each select="fn:distinct-values(LIST_G_LINES/G_LINES/TAXRATE)">
                            <xsl:element name="Values">
                                <xsl:value-of select="."/>
                            </xsl:element>
                        </xsl:for-each>
                    </xsl:variable>
                    <xsl:variable name="vPath" select="LIST_G_LINES/G_LINES/TAXAMOUNT"/>
                    <xsl:for-each select="$vDistinct/Values">
                    <xsl:variable name="vUniqueVal" select="."/>
                        <xsl:for-each select="LIST_G_LINES/G_LINES/TAXRATE=.">
                            <xsl:element name="TaxAmount">
                                <xsl:value-of select="sum($vPath[@TAXRATE = $vUniqueVal])"/>
                                <xsl:value-of select="$vUniqueVal"/>
                            </xsl:element>
                        </xsl:for-each>
                    </xsl:for-each>

This code doesn't seem to work. Could you please help.

Upvotes: 0

Views: 145

Answers (1)

Vebbie
Vebbie

Reputation: 1695

From your input and given XSLT, assuming the input file as below: (if it is XML)

<?xml version="1.0" encoding="UTF-8"?>
<LIST_G_LINES>
  <G_LINES>
    <Taxrates> 20 <Taxamount> 1.60 </Taxamount></Taxrates>
  </G_LINES>
  <G_LINES>
    <Taxrates> 7 <Taxamount> 21.50 </Taxamount></Taxrates>
  </G_LINES>
  <G_LINES>
    <Taxrates> 19 <Taxamount> 1.60 </Taxamount></Taxrates>
  </G_LINES>
  <G_LINES>
    <Taxrates> 19 <Taxamount> 11.68 </Taxamount></Taxrates>
  </G_LINES>
  <G_LINES>
    <Taxrates> 20 <Taxamount> 101 </Taxamount></Taxrates>
  </G_LINES>
</LIST_G_LINES>

An XSLT 2.0 solution can be as following:

<?xml version="1.0" encoding="UTF-8"?>
<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="xs">

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

<xsl:template match="/LIST_G_LINES">
    <xsl:for-each-group select="G_LINES" group-by="Taxrates/text()">
        <H>
            <TaxRate>
                <xsl:value-of select="current-grouping-key()" />
            </TaxRate>
            <TaxAmount>
                <xsl:value-of select="sum(current-group()/Taxrates/Taxamount)" />
            </TaxAmount>
        </H>
    </xsl:for-each-group>
</xsl:template>
</xsl:stylesheet>

Refer here to see the output

Edit: Based on your updated XML, you can try the following XSLT 3.0 solution:

<?xml version="1.0" encoding="UTF-8"?>
<xsl:stylesheet xmlns:xsl="http://www.w3.org/1999/XSL/Transform"
xmlns:xs="http://www.w3.org/2001/XMLSchema" xmlns:math="http://www.w3.org/2005/xpath-functions/math"
exclude-result-prefixes="xs math" version="3.0">

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

<xsl:template match="/">
    <xsl:for-each-group select="DATA/LIST_G_TRX/G_TRX/LIST_G_HEADER/G_HEADER/LIST_G_LINES/G_LINES" composite="yes" group-by="TAXRATE">
        <xsl:if test="current-grouping-key() != ''">
            <H>
                <TaxRate>
                    <xsl:value-of select="current-grouping-key()" />
                </TaxRate>
                <TaxAmount>
                    <xsl:value-of select="sum(current-group()/TAXAMOUNT)" />
                </TaxAmount>
            </H>
        </xsl:if>
    </xsl:for-each-group>
</xsl:template>
</xsl:stylesheet>

Upvotes: 2

Related Questions