XSL Group by Country and Weight

Question

I'm making a pricing chart from an XML that contains many rates (25k+). Each rate row specifies

A service name in @service
A country code (iso-3166) in @iso
A weight in @kg
The price in @pc

For each unique service I want to list

Every possible weight in column headings
List 1 row for every unique country
The 1st cell in the row shows the name (@iso) and then the @pc for the corresponding @kg

The problem I'm having, is that all of the country names are not appearing. Only 2 in Standard and Priority, and none in Express service.

        <h1>Standard</h1>
        <table>
                <tr>
                        <td>FR</td>
                </tr>
                <tr>
                        <td>CA</td>
                </tr>

        </table>
        <h1>Priority</h1>
        <table>
                <tr>
                        <td>DE</td>
                </tr>
                <tr>
                        <td>GB</td>
                </tr>
        </table>
        <h1>Express</h1>
        <table>
           NOTE--Should be 4 countries here
        </table>

I'm hoping to output something like this

 <h1>Standard</h1>
<table>
        <thead>
            <tr>
                <th>Country</th>
                <!-- One column for each unique weight -->
                <th>0.1</th>
                <th>0.15</th>
                <th>0.2</th>
                <th>0.25</th>
                <th>0.3</th>
            </tr>
        </thead>
        <tbody>
            <!-- One row for each unique country -->
            <tr>
                <td>FR</td>
                <!-- One column for each unique weight -->
                <td>202</td>
                <td>302</td>
                <td>402</td>
                <td>502</td>
                <td>602</td>
            </tr>
    
            <tr>
                <td>CA</td>
                <!-- One column for each unique weight -->
                <td>101</td>
                <td>151</td>
                <td>201</td>
                <td>251</td>
                <td>301</td>
            </tr>
    
            <!-- An so on for every unique country -->
        </tbody>
    </table>

Here's my XML / XSL

<xml>
 <rate service="Standard" iso="FR" kg="0.1" pc="202.0000"/>
 <rate service="Standard" iso="CA" kg="0.1" pc="101.0000" />
 <rate service="Standard" iso="CA" kg="0.15" pc="151.0000" />
 <rate service="Standard" iso="FR" kg="0.15" pc="302.0000" />
 <rate service="Standard" iso="FR" kg="0.2" pc="402.0000" />
 <rate service="Standard" iso="CA" kg="0.2" pc="201.0000" />
 <rate service="Standard" iso="CA" kg="0.25" pc="251.0000" />
 <rate service="Standard" iso="FR" kg="0.25" pc="502.0000" />
 <rate service="Standard" iso="FR" kg="0.3" pc="602.0000" />
 <rate service="Standard" iso="CA" kg="0.3" pc="301.0000" />

 <rate service="Priority" iso="CA" kg="0.5" pc="0.6000" />
 <rate service="Priority" iso="FR" kg="0.5" pc="0.6680" />
 <rate service="Priority" iso="DE" kg="0.5" pc="0.6070" />
 <rate service="Priority" iso="GB" kg="0.5" pc="0.7800" />
 <rate service="Priority" iso="GB" kg="0.75" pc="1.1050" />
 <rate service="Priority" iso="DE" kg="0.75" pc="0.8610" />
 <rate service="Priority" iso="FR" kg="0.75" pc="0.9470" />
 <rate service="Priority" iso="CA" kg="0.75" pc="0.8500" />
 <rate service="Priority" iso="CA" kg="1" pc="1.1000" />
 <rate service="Priority" iso="FR" kg="1" pc="1.2250" />
 <rate service="Priority" iso="DE" kg="1" pc="1.1140" />
 <rate service="Priority" iso="GB" kg="1" pc="1.4300" />

 <rate service="Express" iso="FR" kg="0.1" pc="64.6400" />
 <rate service="Express" iso="CA" kg="0.1" pc="101.0000" />
 <rate service="Express" iso="DE" kg="0.1" pc="129.2800" />
 <rate service="Express" iso="GB" kg="0.1" pc="147.6380" />
 <rate service="Express" iso="GB" kg="0.15" pc="220.7260" />
 <rate service="Express" iso="DE" kg="0.15" pc="193.2800" />
 <rate service="Express" iso="CA" kg="0.15" pc="151.0000" />
 <rate service="Express" iso="FR" kg="0.15" pc="96.6400" />
 <rate service="Express" iso="FR" kg="0.2" pc="128.6400" />
 <rate service="Express" iso="CA" kg="0.2" pc="201.0000" />
 <rate service="Express" iso="DE" kg="0.2" pc="257.2800" />
 <rate service="Express" iso="GB" kg="0.2" pc="293.8140" />
 <rate service="Express" iso="GB" kg="0.25" pc="366.9020" />
 <rate service="Express" iso="DE" kg="0.25" pc="321.2800" />
 <rate service="Express" iso="CA" kg="0.25" pc="251.0000" />
 <rate service="Express" iso="FR" kg="0.25" pc="160.6400" />
 <rate service="Express" iso="FR" kg="0.3" pc="192.6400" />
 <rate service="Express" iso="CA" kg="0.3" pc="301.0000" />
 <rate service="Express" iso="DE" kg="0.3" pc="385.2800" />
 <rate service="Express" iso="GB" kg="0.3" pc="439.9900" />
</xml>

And my XSL so far...

<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="service" match="rate" use="@service" />
<xsl:key name="iso" match="rate" use="@iso" />

<!-- Store off all the rates -->
<xsl:variable name='ratelist' select='//rate'/>

<xsl:template match="/">
<xml>
    <!-- Get a list of unique service names -->
    <xsl:variable name='servicelist' select="$ratelist[generate-id()=generate-id(key('service', @service))]"/>
    <xsl:for-each select='$servicelist'>
        <!-- Get the rates for just this service -->
        <xsl:variable name='servicerate' select="$ratelist[@service=current()/@service]"/>

        <!-- Get a list of unique country codes -->
        <xsl:variable name='isolist' select="$servicerate[generate-id()=generate-id(key('iso', @iso))]"/>

        <h1><xsl:value-of select='@service'/></h1>
        <table>
            <!-- Output 1 row for each unique country -->
            <xsl:for-each select='$isolist'>
                <tr>
                    <td><xsl:value-of select='@iso'/></td>
                </tr>
            </xsl:for-each>
        </table>
    </xsl:for-each>
</xml>
</xsl:template>
</xsl:stylesheet>

Please edit your question and add the expected result of transforming the given input example. — michael.hor257k, Apr 01 '22 at 17:39
AFAICT, you are struggling with creating sub-groups within the current group. See if this helps: https://stackoverflow.com/a/58525214/3016153 — michael.hor257k, Apr 01 '22 at 18:05
Thanks, I added the expected output. It shows both the rows (my current issue) and the unique weight columns (maybe my next issue). — William Walseth, Apr 01 '22 at 18:22
The rows are relatively easy (see the link above). Creating a column for each possible weight and populating it with values that represent the intersection of row and column (a value which may or may not exist) is more difficult. Perhaps you should split the question into two or more parts. — michael.hor257k, Apr 01 '22 at 18:32

score 2 · Accepted Answer · answered Apr 01 '22 at 19:11

As I mentioned in the comments, this is not a trivial undertaking. See if you can make sense of the following stylesheet:

XSLT 1.0

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

<xsl:key name="rate-by-service" match="rate" use="@service" />
<xsl:key name="rate-by-country" match="rate" use="concat(@service, '|', @iso)" />
<xsl:key name="rate-by-weight" match="rate" use="concat(@service, '|', @kg)" />
<xsl:key name="value" match="rate" use="concat(@service, '|', @iso, '|', @kg)" />

<xsl:template match="/xml">
    <html>
        <!-- a group for each unique service -->
        <xsl:for-each select="rate[generate-id() = generate-id(key('rate-by-service', @service)[1])]">
            <xsl:variable name="group" select="key('rate-by-service', @service)" />
            <xsl:variable name="service" select="@service" />
            <!-- unique countries in current group  -->
            <xsl:variable name="countries" select="$group[generate-id() = generate-id(key('rate-by-country', concat(@service, '|', @iso))[1])]"/>
            <!-- unique weights in current group  -->
            <xsl:variable name="weights" select="$group[generate-id() = generate-id(key('rate-by-weight', concat(@service, '|', @kg))[1])]"/>
            <!-- output  -->
            <h1>
                <xsl:value-of select='@service'/>
            </h1>
            <table border="1">
                <thead>
                    <tr>
                        <th>Country</th>
                        <!-- a column for each unique weight in current group -->
                        <xsl:for-each select="$weights">
                            <th>
                                <xsl:value-of select='@kg'/>
                            </th>
                        </xsl:for-each>
                    </tr>
                </thead>
                <tbody>
                    <!-- a row for each unique country in current group -->
                    <xsl:for-each select="$countries">
                        <xsl:variable name="iso" select="@iso" />
                        <tr>
                            <td>
                                <xsl:value-of select='@iso'/>
                            </td>
                            <!-- a cell for each unique weight in current group -->
                            <xsl:for-each select="$weights">
                                <td>
                                    <xsl:value-of select="key('value', concat($service, '|', $iso, '|', @kg))/@pc"/>
                                </td>
                            </xsl:for-each>
                        </tr>
                    </xsl:for-each>
                </tbody>
            </table>
        </xsl:for-each>
            
    </html>
</xsl:template>

</xsl:stylesheet>

when applied to your input example, this will produce:

Result

<html>
   <h1>Standard</h1>
   <table border="1">
      <thead>
         <tr>
            <th>Country</th>
            <th>0.1</th>
            <th>0.15</th>
            <th>0.2</th>
            <th>0.25</th>
            <th>0.3</th>
         </tr>
      </thead>
      <tbody>
         <tr>
            <td>FR</td>
            <td>202.0000</td>
            <td>302.0000</td>
            <td>402.0000</td>
            <td>502.0000</td>
            <td>602.0000</td>
         </tr>
         <tr>
            <td>CA</td>
            <td>101.0000</td>
            <td>151.0000</td>
            <td>201.0000</td>
            <td>251.0000</td>
            <td>301.0000</td>
         </tr>
      </tbody>
   </table>
   <h1>Priority</h1>
   <table border="1">
      <thead>
         <tr>
            <th>Country</th>
            <th>0.5</th>
            <th>0.75</th>
            <th>1</th>
         </tr>
      </thead>
      <tbody>
         <tr>
            <td>CA</td>
            <td>0.6000</td>
            <td>0.8500</td>
            <td>1.1000</td>
         </tr>
         <tr>
            <td>FR</td>
            <td>0.6680</td>
            <td>0.9470</td>
            <td>1.2250</td>
         </tr>
         <tr>
            <td>DE</td>
            <td>0.6070</td>
            <td>0.8610</td>
            <td>1.1140</td>
         </tr>
         <tr>
            <td>GB</td>
            <td>0.7800</td>
            <td>1.1050</td>
            <td>1.4300</td>
         </tr>
      </tbody>
   </table>
   <h1>Express</h1>
   <table border="1">
      <thead>
         <tr>
            <th>Country</th>
            <th>0.1</th>
            <th>0.15</th>
            <th>0.2</th>
            <th>0.25</th>
            <th>0.3</th>
         </tr>
      </thead>
      <tbody>
         <tr>
            <td>FR</td>
            <td>64.6400</td>
            <td>96.6400</td>
            <td>128.6400</td>
            <td>160.6400</td>
            <td>192.6400</td>
         </tr>
         <tr>
            <td>CA</td>
            <td>101.0000</td>
            <td>151.0000</td>
            <td>201.0000</td>
            <td>251.0000</td>
            <td>301.0000</td>
         </tr>
         <tr>
            <td>DE</td>
            <td>129.2800</td>
            <td>193.2800</td>
            <td>257.2800</td>
            <td>321.2800</td>
            <td>385.2800</td>
         </tr>
         <tr>
            <td>GB</td>
            <td>147.6380</td>
            <td>220.7260</td>
            <td>293.8140</td>
            <td>366.9020</td>
            <td>439.9900</td>
         </tr>
      </tbody>
   </table>
</html>

Rendered:

Thanks, what a great piece of work! Never thought about using key() like this line -- — William Walseth, Apr 01 '22 at 21:21
But that's an integral part of Muenchian grouping: http://www.jenitennison.com/xslt/grouping/muenchian.html — michael.hor257k, Apr 01 '22 at 21:32

XSL Group by Country and Weight

1 Answers1