Take count of unique values within distinct codes (XSLT 1)

Question

Hi all,

Stuck a bit with the following and would love some ideas/pointers in the right direction. I have the following XML:

<RecordsCollection>
  <CustomerRecord>
    <customerId>12345</customerId>
    <currency>USD</currency>
  </CustomerRecord>
  <CustomerRecord>
    <customerId>12345</customerId>
    <currency>USD</currency>
  </CustomerRecord>
  <CustomerRecord>
    <customerId>90210</customerId>
    <currency>USD</currency>
  </CustomerRecord>
</RecordsCollection>

What I need to accomplish is to simply produce a single value containing the count of unique currency elements contained with a unique account number. To explain further, the example above contains two entries with the same currency (USD) for the same account number (12345), so they're counted as 1 and another entry which is also counted as 1. Hence, the example above should result in:

<totalCount>2</totalCount>

Any idea as to how to achieve it? I know how to select distinct account numbers, but what I don't seem to be able to wrap my head around is how to count the distinct currencies within each account.

Lastly, this has to be done using XSLT 1.0 ... any thoughts would be greatly appreciated!

Answer 1

This stylesheet:

<xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform"&gt;
    <xsl:key name="kRecordByIdAndCurr" match="CustomerRecord"
             use="concat(customerId,'++',currency)"/>
    <xsl:template match="/">
        <totalCount>
            <xsl:value-of select="count(*/*
                                         [count(.|key('kRecordByIdAndCurr',
                                                      concat(customerId,
                                                             '++',
                                                             currency)
                                                     )[1]
                                               )=1])"/>
        </totalCount>
    </xsl:template>
</xsl:stylesheet>

Output:

<totalCount>2</totalCount>

Note: Just grouping by customerId and currency string value.

Edit: Sorry, miss the customerId before.

Answer 2

This transformation:

<xsl:stylesheet version="1.0"
 xmlns:xsl="http://www.w3.org/1999/XSL/Transform"&gt;
 <xsl:output omit-xml-declaration="yes" indent="yes"/>

 <xsl:key name="kCurrencyByIdAndType" match="currency"
  use="concat(../customerId, '+', .)"/>

 <xsl:template match="/*">
  <totalCount>
   <xsl:value-of select=
    "count(*/currency[generate-id(key('kCurrencyByIdAndType',
                                      concat(../customerId, '+', .)
                                      )[1]
                                 )
                    = generate-id()
                    ]
          )
    "/>
  </totalCount>
 </xsl:template>
</xsl:stylesheet>

when applied on the provided XML document:

<RecordsCollection>
  <CustomerRecord>
    <customerId>12345</customerId>
    <currency>USD</currency>
  </CustomerRecord>
  <CustomerRecord>
    <customerId>12345</customerId>
    <currency>USD</currency>
  </CustomerRecord>
  <CustomerRecord>
    <customerId>90210</customerId>
    <currency>USD</currency>
  </CustomerRecord>
</RecordsCollection>

produces the wanted, correct result:

<totalCount>2</totalCount>

Explanation: The Muenchian method for grouping is used on a composite key that can be expressed as the concatenation of the string values of any currency and its customerId sibling.