We help IT Professionals succeed at work.

XSLT to sum totals and grouping

High Priority
123 Views
Last Modified: 2018-12-15
Hi All,

I am working on an xslt which should transform XML to space separated flat file. I was able to do xslt which essentially as per requirement is space delimited and restricts record to 300 characters.

My output at the moment is

003339334070 0000554509 0000000200 10192018 EUNICE PRICE
003339334070 0000554510 0000005750 10192018 M3 USA Corporation
003339334071 0000554509 0000000300 10192018 EUNICE PRICE
003339334071 0000554510 0000005700 10192018 M3 USA Corporation

But vendor came back with new requirement to add another record which should be sum  total amount and count the line items. For example I need the data like below. Where 2 is number of lines and 003339334070 is feild wd:CF_Account_number_HSBC_Canada from source. This field is also key to group the records. And  0000005950 is sum of two totals.

003339334070 0000554509 0000000200 10192018 EUNICE PRICE
003339334070 0000554510 0000005750 10192018 M3 USA Corporation
003339334070 2                     0000005950
003339334071 0000554509 0000000300 10192018 EUNICE PRICE
003339334071 0000554510 0000005700 10192018 M3 USA Corporation
003339334071 2                     0000006000

I am attaching the XSLT and XML i am using for testing. Can some one please help me to achieve above result? I am new to XSLT and learning.

Thanks & Regards,
Sridhar
XSLTHELP.xsl
pp2.xml
Comment
Watch Question

Information Architect
CERTIFIED EXPERT
Top Expert 2006
Commented:
<?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:wd="urn:com.workday.report/Positive_Pay_SVB"
    xmlns:this="urn:this-stylesheet" exclude-result-prefixes="xs" version="2.0">
    
    <xsl:output method="text"/>
    <xsl:variable name="delimiter" select="'&#x20;'"/>
    <xsl:variable name="linefeed" select="'&#xD;&#xA;'"/>
    
    <xsl:template match="wd:Report_Data">
        <xsl:for-each-group select="wd:Report_Entry/wd:Payments_group" group-by="wd:CF_Account_number_HSBC_Canada">
            <xsl:apply-templates select="current-group()"/>
            <xsl:value-of select="wd:CF_Account_number_HSBC_Canada"/>
            <xsl:value-of select="$delimiter"/>
            <xsl:value-of select="count(current-group())"/>
            <xsl:value-of select="string-join(for $i in 1 to 11 return if($i > string-length(string(count(current-group())))) then($delimiter) else (''), '')"/>
            <xsl:value-of select="format-number(sum(current-group()/number(translate(wd:Payment_Total_Amount,'.',''))), '0000000000')"/>
            <xsl:value-of select='$linefeed'/>
        </xsl:for-each-group>
     </xsl:template>
    
    <xsl:template match="wd:Report_Entry/wd:Payments_group">
        
        <xsl:variable name="Paymentamount" select="translate(normalize-space(translate(wd:Payment_Total_Amount,'.','')),'','')"/>
        <xsl:variable name="Var_Payment" select="concat(substring('0000000000', 1 + string-length($Paymentamount)), $Paymentamount)"/>
        <xsl:variable name="Var_CheckNumber" select="concat(substring('0000000000', 1 + string-length(wd:Check_Number)), wd:Check_Number)"/>
        
        <xsl:value-of select="substring(concat (wd:CF_Account_number_HSBC_Canada, $delimiter, $Var_CheckNumber, $delimiter, $Var_Payment, $delimiter, wd:Payment_Date, $delimiter, wd:Payment_Payee/@wd:Descriptor),1,300)"/>
        <xsl:value-of select='$linefeed'/>
        
    </xsl:template>    
</xsl:stylesheet>

Open in new window

Gertone (Geert Bormans)Information Architect
CERTIFIED EXPERT
Top Expert 2006

Commented:
Try that
I would use format-number() for the 0 padded outline of the numbers, you can clean stuff up with some functions too

Author

Commented:
Hi Geert,

Thanks a lot for the help. I served my purpose exactly.

Best Regards,
Sridhar
Gertone (Geert Bormans)Information Architect
CERTIFIED EXPERT
Top Expert 2006

Commented:
welcome