0

I'm fairly new to XSLT and stuck on a current problem. I've done some searches throughout Stackflow (seems like Muenchian method is the common group method) but I can't seem to mimic some of the posted ideas as of yet.

So I'm using a line item read system of which I'm trying to write code in XSLT to read every line to check if the supplier ID is the same, if true, it will aggregate this into one line, then sum the amounts. If not true, it should start a new line with the ID and sum the amount and so forth. I am using xml version='1.0'

Below is my current data file in XML:

<data>
<row>
    <column1>06-11111</column1>
    <column2>CP</column2>
    <column3>744.04</column3>
    <column4>CAD</column4>
</row>
<row>
    <column1>06-11111</column1>
    <column2>CP</column2>
    <column3>105.09</column3>
    <column4>CAD</column4>
</row>
<row>
    <column1>06-11111</column1>
    <column2>CP</column2>
    <column3>1366.24</column3>
    <column4>CAD</column4>
</row>
<row>
    <column1>06-11111</column1>
    <column2>CP</column2>
    <column3>485.71</column3>
    <column4>CAD</column4>
</row>
<row>
    <column1>06-11112</column1>
    <column2>Ever</column2>
    <column3>459.60</column3>
    <column4>CAD</column4>
</row>
<row>
    <column1>06-11112</column1>
    <column2>Ever</column2>
    <column3>409.14</column3>
    <column4>CAD</column4>
</row>
<row>
    <column1>06-11112</column1>
    <column2>Ever</column2>
    <column3>397.12</column3>
    <column4>CAD</column4>
</row>
<row>
    <column1>06-11113</column1>
    <column2>GE</column2>
    <column3>1425</column3>
    <column4>CAD</column4>
</row>
<row>
    <column1>06-11114</column1>
    <column2>Husky</column2>
    <column3>-215.14</column3>
    <column4>USD</column4>
</row>
<row>
    <column1>06-11114</column1>
    <column2>Husky</column2>
    <column3>2015</column3>
    <column4>USD</column4>
</row>
<row>
    <column1>06-11114</column1>
    <column2>Husky</column2>
    <column3>11195.34</column3>
    <column4>USD</column4>
</row>
</data>

The output I would like to achieve after running the XSLT is

06-11111 | CP |2701.08

06-11112 | Ever |1265.86

06-11113 | GE |1425

06-11114 | Husky |12995.20

Any help to get me started would be fantastic!

Echan
  • 5
  • 3
  • If your input is XML, then please show it as XML. For grouping in XSLT 1.0, start here: http://www.jenitennison.com/xslt/grouping/muenchian.html – michael.hor257k Mar 07 '17 at 15:25
  • Thank you for the reply michael.hor257k! edited my question to provide the XML format – Echan Mar 07 '17 at 18:03
  • The answer is in the article I referenced earlier. And in the many examples of Muenchian grouping posted here on SO. – michael.hor257k Mar 07 '17 at 18:05

1 Answers1

0

Here is the grouping using the Muenchian method. I'll let you play with getting the numbers formatted correctly based on the number of decimal points. I typically don't use this because it's limited, tricky and doesn't lend itself to push programming. But, it will work for you today. 

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

<xsl:key name="rows" match="row" use="concat(column1, '||', column2)" />

<xsl:template match="data">
  <xsl:for-each select="row[generate-id(.) = generate-id(key('rows', concat(column1, '||', column2))[1])]">
    <xsl:sort select="column1" data-type="text" order="ascending"/>
    <xsl:sort select="column2" data-type="text" order="ascending"/>

    <xsl:value-of select="concat(column1,'|',column2,'|')"/>

    <xsl:variable name="mySum">
      <xsl:value-of select="sum(key('rows', concat(column1, '||', column2))/column3)"/>
    </xsl:variable>

    <xsl:value-of select="format-number($mySum,'#,##0.00')"/>

    <xsl:value-of select="'&#xD;&#xA;'"/>
  </xsl:for-each>
</xsl:template>
John Ernst
  • 1,206
  • 1
  • 7
  • 11