Count Unique Elements

[aswolff]

I have the below XML and XSLT. I need to create a simple report that counts unique <RECKEY> elements sums the <INVOICE_AMOUNT>. I am missing something since it's not counting unique records but all records.

The result should be:

Unique Count: 3 Total Amount: $1351.32

Thanks in advance!

<?xml version="1.0" encoding="UTF-8"?>
<ROOT>
    <TABLE>
        <ROW>
            <RECKEY>50001489LTEST_D2</RECKEY>
            <INVOICE_AMOUNT>1034.3</INVOICE_AMOUNT>
        </ROW>
        <ROW>
            <RECKEY>50001489LTEST_D2</RECKEY>
            <INVOICE_AMOUNT>1034.3</INVOICE_AMOUNT>
        </ROW>
        <ROW>
            <RECKEY>50001489LTEST_D2</RECKEY>
            <INVOICE_AMOUNT>1034.3</INVOICE_AMOUNT>
        </ROW>
        <ROW>
            <RECKEY>50001489LTEST_D2</RECKEY>
            <INVOICE_AMOUNT>1034.3</INVOICE_AMOUNT>
        </ROW>
        <ROW>
            <RECKEY>50001538AP-41168</RECKEY>
            <INVOICE_AMOUNT>144.62</INVOICE_AMOUNT>
        </ROW>
        <ROW>
            <RECKEY>50001538AP-41169</RECKEY>
            <INVOICE_AMOUNT>172.4</INVOICE_AMOUNT>
        </ROW>
    </TABLE>
</ROOT>

xslt

<?xml version="1.0" encoding="UTF-8"?>
<xsl:stylesheet xmlns:xsl="[URL unfurl="true"]http://www.w3.org/1999/XSL/Transform"[/URL] version="1.0">
    <xsl:output method="xml" encoding="UTF-8" indent="yes"/>
    <xsl:key name="recordkey" match="/ROOT/TABLE/ROW" use="RECKEY"/>

<xsl:template match="/ROOT">
    Count <xsl:value-of select="count(//TABLE/ROW/RECKEY)"/>
    Sum <xsl:value-of select="format-number(sum(//TABLE/ROW/INVOICE_AMOUNT), '0.00')"/>
</xsl:template>
</xsl:stylesheet>

 

[aswolff]

Oh...Btw I think I need to perform a muenchian grouping based on RECKEY (that's why I defined the key) but I have no idea how to implement the key to count and sum the unique records.

 

[aswolff]

I finally figured out a problem on my own:

<InvoiceCount>
                <xsl:value-of
                    select="count(/ROOT/TABLE/ROW[generate-id(.) = generate-id(key('recordkey',RECKEY)[1])])"
                />
            </InvoiceCount>
            <InvoiceTotals>
                <xsl:value-of
                    select="sum(/ROOT/TABLE/ROW[generate-id(.) = generate-id(key('recordkey',RECKEY)[1])]/INVOICE_AMOUNT)"
                />
            </InvoiceTotals>

 

[k5tm]

As noted in a previous thread, grouping is covered in faq426-6585.

Using the XSLT found there, you will discover comments in the code that indicate that the outer xsl:for-each iterates once per unique value of the key. Consider the use of the count() function on the same nodeset.

The inner xsl:for-each iterates through the keys with identical values. In your previous thread thread426-1363579 the ability to sum using recursion was taught, as well as the simpler sum() function. You can make a choice depending on the reliability of the numeric data.

Finally, a small word about courtesy. I had a look back at your three most recent threads in this forum.[ul][li]thread426-1321743[/li][li]thread426-1363579[/li][li]thread426-1376013[/li][/ul]Common to each of these is the fact that the last post was from the k5tm fella. Please make an effort to return to your threads and report success or failure. If we presume that others read these threads (which I think is true) then having that closing feedback gives the later reader some knowledge about the quality of the proposed solution. And, in the case of the CDATA, I am genuinely curious about the need for such extreme use of CDATA.



Tom Morrison

http://www.liant.com

 

[k5tm]

Oops, cross post!
I see from your solution that I misread the original post. Good job! Soon you will be the XML Forum guru!

Tom Morrison

http://www.liant.com