DEVHIDE
Login Or Sign up

Need Help in Munechian grouping to calculate the for-each variable outputs

71 Views Asked by At

Please help in summation logic where I am getting an error while adding the variable value.

XML below

<OutboundPaymentInstruction>
    <OutboundPayment>
        <DocumentPayable>
            <DocumentPayableLine>
                <LineNumber>2</LineNumber>
                <PONumber/>
                <ConsumptionAdviceNumber/>
                <LineType>
                    <Code>TAX</Code>
                    <Meaning/>
                </LineType>
                <LineDescription>Utilities; Justification: W-IN-18-6402-1237845 </LineDescription>
                <LineGrossAmount>
                    <Value>7912.5</Value>
                    <Currency>
                        <Code>THB</Code>
                        <NumericCode>764</NumericCode>
                    </Currency>
                </LineGrossAmount>
                <UnitPrice>0</UnitPrice>
                <Quantity/>
                <UnitOfMeasure>
                    <Code/>
                    <Meaning/>
                </UnitOfMeasure>
                <Tax>
                    <TaxCode>TH VAT</TaxCode>
                    <TaxRate>7</TaxRate>
                    <TaxRateCode>TH AP STD SVC 7</TaxRateCode>
                </Tax>
            </DocumentPayableLine>
            <DocumentPayableLine>
                <LineNumber>3</LineNumber>
                <PONumber/>
                <ConsumptionAdviceNumber/>
                <LineType>
                    <Code>AWT</Code>
                    <Meaning/>
                </LineType>
                <LineDescription>Utilities; Justification: W-IN-18-6402-1237845 </LineDescription>
                <LineGrossAmount>
                    <Value>-3391.07</Value>
                    <Currency>
                        <Code>THB</Code>
                        <NumericCode>764</NumericCode>
                    </Currency>
                </LineGrossAmount>
                <UnitPrice>0</UnitPrice>
                <Quantity/>
                <UnitOfMeasure>
                    <Code/>
                    <Meaning/>
                </UnitOfMeasure>
                <Tax>
                    <TaxCode>TH WHT</TaxCode>
                    <TaxRate>2.999999</TaxRate>
                    <TaxRateCode>TH WHT SVCS 03</TaxRateCode>
                </Tax>
            </DocumentPayableLine>
            <DocumentPayableLine>
                <LineNumber>1</LineNumber>
                <PONumber/>
                <ConsumptionAdviceNumber/>
                <LineType>
                    <Code>ITEM</Code>
                    <Meaning/>
                </LineType>
                <LineDescription>Utilities;Justification: W-IN-18-6402-1237845 </LineDescription>
                <LineGrossAmount>
                    <Value>113035.71</Value>
                    <Currency>
                        <Code>THB</Code>
                        <NumericCode>764</NumericCode>
                    </Currency>
                </LineGrossAmount>
                <UnitPrice>0</UnitPrice>
                <Quantity/>
                <UnitOfMeasure>
                    <Code/>
                    <Meaning/>
                </UnitOfMeasure>
                <Tax>
                    <TaxCode/>
                    <TaxRate>0</TaxRate>
                    <TaxRateCode/>
                </Tax>
            </DocumentPayableLine>
        </DocumentPayable>
    </OutboundPayment>
    <OutboundPayment>
        <DocumentPayable>
            <DocumentPayableLine>
                <LineNumber>2</LineNumber>
                <PONumber/>
                <ConsumptionAdviceNumber/>
                <LineType>
                    <Code>TAX</Code>
                    <Meaning/>
                </LineType>
                <LineDescription>The Garbage Service Fee For 02/64</LineDescription>
                <LineGrossAmount>
                    <Value>6730.5</Value>
                    <Currency>
                        <Code>THB</Code>
                        <NumericCode>764</NumericCode>
                    </Currency>
                </LineGrossAmount>
                <UnitPrice>0</UnitPrice>
                <Quantity/>
                <UnitOfMeasure>
                    <Code/>
                    <Meaning/>
                </UnitOfMeasure>
                <Tax>
                    <TaxCode>TH VAT</TaxCode>
                    <TaxRate>7</TaxRate>
                    <TaxRateCode>TH AP STD SVC 7</TaxRateCode>
                </Tax>
            </DocumentPayableLine>
            <DocumentPayableLine>
                <LineNumber>3</LineNumber>
                <PONumber/>
                <ConsumptionAdviceNumber/>
                <LineType>
                    <Code>AWT</Code>
                    <Meaning/>
                </LineType>
                <LineDescription>The Garbage Service Fee For 02/64</LineDescription>
                <LineGrossAmount>
                    <Value>-2884.5</Value>
                    <Currency>
                        <Code>THB</Code>
                        <NumericCode>764</NumericCode>
                    </Currency>
                </LineGrossAmount>
                <UnitPrice>0</UnitPrice>
                <Quantity/>
                <UnitOfMeasure>
                    <Code/>
                    <Meaning/>
                </UnitOfMeasure>
                <Tax>
                    <TaxCode>TH WHT</TaxCode>
                    <TaxRate>3</TaxRate>
                    <TaxRateCode>TH WHT SVCS 03</TaxRateCode>
                </Tax>
            </DocumentPayableLine>
            <DocumentPayableLine>
                <LineNumber>1</LineNumber>
                <PONumber/>
                <ConsumptionAdviceNumber/>
                <LineType>
                    <Code>ITEM</Code>
                    <Meaning/>
                </LineType>
                <LineDescription>The Garbage Service Fee For 02/64</LineDescription>
                <LineGrossAmount>
                    <Value>96150</Value>
                    <Currency>
                        <Code>THB</Code>
                        <NumericCode>764</NumericCode>
                    </Currency>
                </LineGrossAmount>
                <UnitPrice>0</UnitPrice>
                <Quantity/>
                <UnitOfMeasure>
                    <Code/>
                    <Meaning/>
                </UnitOfMeasure>
                <Tax>
                    <TaxCode/>
                    <TaxRate>0</TaxRate>
                    <TaxRateCode/>
                </Tax>
            </DocumentPayableLine>
        </DocumentPayable>
        <DocumentPayable>
            <DocumentPayableLine>
                <LineNumber>2</LineNumber>
                <PONumber/>
                <ConsumptionAdviceNumber/>
                <LineType>
                    <Code>TAX</Code>
                    <Meaning/>
                </LineType>
                <LineDescription> Monthly billing on Feb'21 of supplier 304 INDUSTRIAL PARK.( Waste Water )</LineDescription>
                <LineGrossAmount>
                    <Value>28276.42</Value>
                    <Currency>
                        <Code>THB</Code>
                        <NumericCode>764</NumericCode>
                    </Currency>
                </LineGrossAmount>
                <UnitPrice>0</UnitPrice>
                <Quantity/>
                <UnitOfMeasure>
                    <Code/>
                    <Meaning/>
                </UnitOfMeasure>
                <Tax>
                    <TaxCode>TH VAT</TaxCode>
                    <TaxRate>7</TaxRate>
                    <TaxRateCode>TH AP STD SVC 7</TaxRateCode>
                </Tax>
            </DocumentPayableLine>
            <DocumentPayableLine>
                <LineNumber>3</LineNumber>
                <PONumber/>
                <ConsumptionAdviceNumber/>
                <LineType>
                    <Code>AWT</Code>
                    <Meaning/>
                </LineType>
                <LineDescription> Monthly billing on Feb'21 of supplier 304 INDUSTRIAL PARK.( Waste Water )</LineDescription>
                <LineGrossAmount>
                    <Value>-12118.46</Value>
                    <Currency>
                        <Code>THB</Code>
                        <NumericCode>764</NumericCode>
                    </Currency>
                </LineGrossAmount>
                <UnitPrice>0</UnitPrice>
                <Quantity/>
                <UnitOfMeasure>
                    <Code/>
                    <Meaning/>
                </UnitOfMeasure>
                <Tax>
                    <TaxCode>TH WHT</TaxCode>
                    <TaxRate>2.999999</TaxRate>
                    <TaxRateCode>TH WHT SVCS 03</TaxRateCode>
                </Tax>
            </DocumentPayableLine>
            <DocumentPayableLine>
                <LineNumber>1</LineNumber>
                <PONumber/>
                <ConsumptionAdviceNumber/>
                <LineType>
                    <Code>ITEM</Code>
                    <Meaning/>
                </LineType>
                <LineDescription> Monthly billing on Feb'21 of supplier 304 INDUSTRIAL PARK.( Waste Water )</LineDescription>
                <LineGrossAmount>
                    <Value>403948.8</Value>
                    <Currency>
                        <Code>THB</Code>
                        <NumericCode>764</NumericCode>
                    </Currency>
                </LineGrossAmount>
                <UnitPrice>0</UnitPrice>
                <Quantity/>
                <UnitOfMeasure>
                    <Code/>
                    <Meaning/>
                </UnitOfMeasure>
                <Tax>
                    <TaxCode/>
                    <TaxRate>0</TaxRate>
                    <TaxRateCode/>
                </Tax>
            </DocumentPayableLine>
        </DocumentPayable>
        <DocumentPayable>
            <DocumentPayableLine>
                <LineNumber>2</LineNumber>
                <PONumber/>
                <ConsumptionAdviceNumber/>
                <LineType>
                    <Code>TAX</Code>
                    <Meaning/>
                </LineType>
                <LineDescription>Monthly billing on Feb'21 of supplier 304 INDUSTRIAL PARK.( Waste Water )</LineDescription>
                <LineGrossAmount>
                    <Value>13296.15</Value>
                    <Currency>
                        <Code>THB</Code>
                        <NumericCode>764</NumericCode>
                    </Currency>
                </LineGrossAmount>
                <UnitPrice>0</UnitPrice>
                <Quantity/>
                <UnitOfMeasure>
                    <Code/>
                    <Meaning/>
                </UnitOfMeasure>
                <Tax>
                    <TaxCode>TH VAT</TaxCode>
                    <TaxRate>7</TaxRate>
                    <TaxRateCode>TH AP STD SVC 7</TaxRateCode>
                </Tax>
            </DocumentPayableLine>
            <DocumentPayableLine>
                <LineNumber>3</LineNumber>
                <PONumber/>
                <ConsumptionAdviceNumber/>
                <LineType>
                    <Code>AWT</Code>
                    <Meaning/>
                </LineType>
                <LineDescription>Monthly billing on Feb'21 of supplier 304 INDUSTRIAL PARK.( Waste Water )</LineDescription>
                <LineGrossAmount>
                    <Value>-5698.35</Value>
                    <Currency>
                        <Code>THB</Code>
                        <NumericCode>764</NumericCode>
                    </Currency>
                </LineGrossAmount>
                <UnitPrice>0</UnitPrice>
                <Quantity/>
                <UnitOfMeasure>
                    <Code/>
                    <Meaning/>
                </UnitOfMeasure>
                <Tax>
                    <TaxCode>TH WHT</TaxCode>
                    <TaxRate>3</TaxRate>
                    <TaxRateCode>TH WHT SVCS 03</TaxRateCode>
                </Tax>
            </DocumentPayableLine>
            <DocumentPayableLine>
                <LineNumber>1</LineNumber>
                <PONumber/>
                <ConsumptionAdviceNumber/>
                <LineType>
                    <Code>ITEM</Code>
                    <Meaning/>
                </LineType>
                <LineDescription>Monthly billing on Feb'21 of supplier 304 INDUSTRIAL PARK.( Waste Water )</LineDescription>
                <LineGrossAmount>
                    <Value>189945</Value>
                    <Currency>
                        <Code>THB</Code>
                        <NumericCode>764</NumericCode>
                    </Currency>
                </LineGrossAmount>
                <UnitPrice>0</UnitPrice>
                <Quantity/>
                <UnitOfMeasure>
                    <Code/>
                    <Meaning/>
                </UnitOfMeasure>
                <Tax>
                    <TaxCode/>
                    <TaxRate>0</TaxRate>
                    <TaxRateCode/>
                </Tax>
            </DocumentPayableLine>
        </DocumentPayable>
        <DocumentPayable>
            <DocumentPayableLine>
                <LineNumber>2</LineNumber>
                <PONumber/>
                <ConsumptionAdviceNumber/>
                <LineType>
                    <Code>TAX</Code>
                    <Meaning/>
                </LineType>
                <LineDescription>Monthly billing on Feb'21 of supplier 304 INDUSTRIAL PARK.( Rental Car parking ) Cloud GL : 630086 Cloud CC : 50020 Invoice = VAT 0%</LineDescription>
                <LineGrossAmount>
                    <Value>0</Value>
                    <Currency>
                        <Code>THB</Code>
                        <NumericCode>764</NumericCode>
                    </Currency>
                </LineGrossAmount>
                <UnitPrice>0</UnitPrice>
                <Quantity/>
                <UnitOfMeasure>
                    <Code/>
                    <Meaning/>
                </UnitOfMeasure>
                <Tax>
                    <TaxCode>TH VAT</TaxCode>
                    <TaxRate>0</TaxRate>
                    <TaxRateCode>TH AP ZERO 0</TaxRateCode>
                </Tax>
            </DocumentPayableLine>
            <DocumentPayableLine>
                <LineNumber>3</LineNumber>
                <PONumber/>
                <ConsumptionAdviceNumber/>
                <LineType>
                    <Code>AWT</Code>
                    <Meaning/>
                </LineType>
                <LineDescription>Monthly billing on Feb'21 of supplier 304 INDUSTRIAL PARK.( Rental Car parking ) Cloud GL : 630086 Cloud CC : 50020 Invoice = VAT 0%</LineDescription>
                <LineGrossAmount>
                    <Value>-13763.2</Value>
                    <Currency>
                        <Code>THB</Code>
                        <NumericCode>764</NumericCode>
                    </Currency>
                </LineGrossAmount>
                <UnitPrice>0</UnitPrice>
                <Quantity/>
                <UnitOfMeasure>
                    <Code/>
                    <Meaning/>
                </UnitOfMeasure>
                <Tax>
                    <TaxCode>TH WHT</TaxCode>
                    <TaxRate>5</TaxRate>
                    <TaxRateCode>TH WHT RENT 05</TaxRateCode>
                </Tax>
            </DocumentPayableLine>
            <DocumentPayableLine>
                <LineNumber>1</LineNumber>
                <PONumber/>
                <ConsumptionAdviceNumber/>
                <LineType>
                    <Code>ITEM</Code>
                    <Meaning/>
                </LineType>
                <LineDescription>Monthly billing on Feb'21 of supplier 304 INDUSTRIAL PARK.( Rental Car parking )Cloud GL : 630086 Cloud CC : 50020 Invoice = VAT 0%</LineDescription>
                <LineGrossAmount>
                    <Value>275264</Value>
                    <Currency>
                        <Code>THB</Code>
                        <NumericCode>764</NumericCode>
                    </Currency>
                </LineGrossAmount>
                <UnitPrice>0</UnitPrice>
                <Quantity/>
                <UnitOfMeasure>
                    <Code/>
                    <Meaning/>
                </UnitOfMeasure>
                <Tax>
                    <TaxCode/>
                    <TaxRate>0</TaxRate>
                    <TaxRateCode/>
                </Tax>
            </DocumentPayableLine>
        </DocumentPayable>
    </OutboundPayment>
</OutboundPaymentInstruction>

Please help as I am trying to do the below logic for individual "AWT" scenario and finally summing up the total by grouping by TaxRateCode for its own OutboundPayment tag.

Forumla trying to implement = (100 div (Tax/TaxRate)) * LineGrossAmount/Value

Example - ((100 div 3) * -5698.35) = -189945
          ((100 div 2.999999) * -12118.46) = -403948.8013
          ((100 div 3) * -2884.5) = -96150

 <calc>690043.80</calc>

Expected Output

<Result>       
                <Rcrd>
                  <TaxAmt>
                     <Rate>3</Rate>
                     <calc>690043.80</calc>
                  </TaxAmt>
               </Rcrd>
               <Rcrd>
                  <TaxAmt>
                     <Rate>5</Rate>
                     <calc>275264.00</calc>
                  </TaxAmt>
               </Rcrd>
               
</Result>
<Result>
               <Rcrd>
                  <TaxAmt>
                     <Rate>3</Rate>
                     <calc>113035.7043</calc>
                  </TaxAmt>
               </Rcrd>
</Result>

Code Tried

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

<xsl:key name="k" match="DocumentPayableLine[LineType/Code='AWT']" use="concat(generate-id(../..), Tax/TaxRateCode)" />

<xsl:template match="/OutboundPaymentInstruction">
    <xsl:for-each select="OutboundPayment">
        <Result>
            <xsl:for-each select="DocumentPayable/DocumentPayableLine[LineType/Code='AWT'][count(. | key('k', concat(generate-id(../..), Tax/TaxRateCode))[1]) = 1]">
                <Rcrd>
                         <xsl:variable name="TX1"><xsl:value-of select="Tax/TaxRate"/></xsl:variable>
                         <xsl:variable name="GrossAmount"><xsl:value-of select="LineGrossAmount/Value"/></xsl:variable>         
                    <TaxAmt>
                        <Rate>
                            <xsl:value-of select="round(Tax/TaxRate)"/>
                        </Rate>
                        
                        <xsl:variable name="IndividualCalc"><xsl:value-of select="((100 div $TX1) * $GrossAmount)"/></xsl:variable>
                        <calc>      
                         <xsl:value-of select="-sum(key('k', concat(generate-id(../..), Tax/TaxRateCode))$IndividualCalc)"/>
                        </calc>
                    </TaxAmt>
                </Rcrd>
            </xsl:for-each>
        </Result>
    </xsl:for-each>
    
    </xsl:template>

</xsl:stylesheet>

Please help.

xslt grouping xslt-1.0 xslt-grouping muenchian-grouping
Original Q&A
1

There are 1 best solutions below

3
Martin Honnen On

Without the expressive power of XPath 2 or higher I think, one way is to store the individual values in an XML result tree fragment and convert it to a node-set for summing the values up:

<xsl:template match="/OutboundPaymentInstruction">
    <xsl:for-each select="OutboundPayment">
        <Result>
            <xsl:for-each select="DocumentPayable/DocumentPayableLine[LineType/Code='AWT'][count(. | key('k', concat(generate-id(../..), Tax/TaxRateCode))[1]) = 1]">
                <xsl:variable name="amounts">
                  <xsl:for-each select="key('k', concat(generate-id(../..), Tax/TaxRateCode))">
                    <amount>
                      <xsl:value-of select="100 div Tax/TaxRate * LineGrossAmount/Value"/>
                    </amount>
                  </xsl:for-each>
                </xsl:variable>
                <Rcrd>
                    <TaxAmt>
                        <Rate>
                            <xsl:value-of select="round(Tax/TaxRate)"/>
                        </Rate>
                        
                        <calc>      
                         <xsl:value-of select="format-number(-sum(exsl:node-set($amounts)/amount), '0.00')"/>
                        </calc>
                    </TaxAmt>
                </Rcrd>
            </xsl:for-each>
        </Result>
    </xsl:for-each>
</xsl:template>

I get the values you have shown but in a different order:

<Result>
  <Rcrd>
    <TaxAmt>
      <Rate>3</Rate>
      <calc>113035.70</calc>
    </TaxAmt>
  </Rcrd>
</Result>
<Result>
  <Rcrd>
    <TaxAmt>
      <Rate>3</Rate>
      <calc>690043.80</calc>
    </TaxAmt>
  </Rcrd>
  <Rcrd>
    <TaxAmt>
      <Rate>5</Rate>
      <calc>275264.00</calc>
    </TaxAmt>
  </Rcrd>
</Result>

The XSLT 1 solution needs you to declare xmlns:exsl="http://exslt.org/common" in the XSLT stylesheet (or, for MSXML, change it to a proprietary xmlns:msxml="urn:schemas-microsoft-com:xslt" and use msxml:node-set instead of exsl:node-set).

Related Questions in XSLT

Related Questions in GROUPING

Related Questions in XSLT-1.0

Related Questions in XSLT-GROUPING

Related Questions in MUENCHIAN-GROUPING

Trending Questions

Popular # Hahtags

javascript python java c# php android html jquery c++ css ios sql mysql r reactjs

Popular Questions

.

Copyright © 2021 Jogjafile Inc.