CODEWITHSUNDEEP
xmlxslt
Soham Shah
Soham Shah

Reputation: 289

XSLT to count number of rows matching a field, within each month

My XML is like following:

<?xml version="1.0" encoding="utf-8"?>
<Rowsets DateCreated="2012-08-01T17:43:31" EndDate="2012-08-01T17:43:03" StartDate="2012-08-01T16:43:03" Version="12.0.12 Build(9)">
<Rowset>
    <Columns>
        <Column Description="FirstName" MaxRange="1" MinRange="0" Name="Plant" SQLDataType="12" SourceColumn="Plant"/>
        <Column Description="Lastname" MaxRange="1" MinRange="0" Name="Process" SQLDataType="12" SourceColumn="Process"/>
        <Column Description="BorninMonth" MaxRange="1" MinRange="0" Name="ScheduleStartTimestamp" SQLDataType="93" SourceColumn="ScheduleStartTimestamp"/>
        <Column Description="BorninWeek" MaxRange="1" MinRange="0" Name="PO" SQLDataType="4" SourceColumn="PO"/>
        <Column Description="Phone" MaxRange="1" MinRange="0" Name="EquipmentName" SQLDataType="-1" SourceColumn="EquipmentName"/>
    </Columns>
    <Row>
        <FirstName>Derek</FirstName>
        <Lastname>Shah</Lastname>
        <BorninMonth>1</BorninMonth>
        <BorninWeek>1</BorninWeek>
        <Phone>925</Phone>
    </Row>
    <Row>
        <FirstName>Michael</FirstName>
        <Lastname>Shah</Lastname>
        <BorninMonth>1</BorninMonth>
        <BorninWeek>2</BorninWeek>
        <Phone>925</Phone>
    </Row>
    <Row>
        <FirstName>Yang</FirstName>
        <Lastname>Patel</Lastname>
        <BorninMonth>1</BorninMonth>
        <BorninWeek>1</BorninWeek>
        <Phone>925</Phone>
    </Row>
    <Row>
        <FirstName>Bruke</FirstName>
        <Lastname>Shah</Lastname>
        <BorninMonth>1</BorninMonth>
        <BorninWeek>1</BorninWeek>
        <Phone>925</Phone>
    </Row>
    <Row>
        <FirstName>Maulik</FirstName>
        <Lastname>Shah</Lastname>
        <BorninMonth>2</BorninMonth>
        <BorninWeek>2</BorninWeek>
        <Phone>925</Phone>
    </Row>
    <Row>
        <FirstName>Montu</FirstName>
        <Lastname>Shah</Lastname>
        <BorninMonth>3</BorninMonth>
        <BorninWeek>1</BorninWeek>
        <Phone>925</Phone>
    </Row>
    <Row>
        <FirstName>CHintu</FirstName>
        <Lastname>Shah</Lastname>
        <BorninMonth>4</BorninMonth>
        <BorninWeek>1</BorninWeek>
        <Phone>925</Phone>
    </Row>
    <Row>
        <FirstName>Pintu</FirstName>
        <Lastname>Shah</Lastname>
        <BorninMonth>4</BorninMonth>
        <BorninWeek>1</BorninWeek>
        <Phone>925</Phone>
    </Row>
    <Row>
        <FirstName>Puppy</FirstName>
        <Lastname>Patel</Lastname>
        <BorninMonth>4</BorninMonth>
        <BorninWeek>1</BorninWeek>
        <Phone>925</Phone>
    </Row>
    <Row>
        <FirstName>Tommy</FirstName>
        <Lastname>Shah</Lastname>
        <BorninMonth>5</BorninMonth>
        <BorninWeek>1</BorninWeek>
        <Phone>925</Phone>
    </Row>
</Rowset>

Now I want to find out the count of born in month 1 and with last name Shah. Same way Born in Month 2 and last name Shah. Born in Month 3 and Last Name Shah.

So my output XML will look like:

<?xml version="1.0" encoding="utf-8"?>
<Rowsets DateCreated="2012-08-01T17:43:31" EndDate="2012-08-01T17:43:03" StartDate="2012-08-01T16:43:03" Version="12.0.12 Build(9)">
<Rowset>
    <Columns>
        <Column Description="FirstName" MaxRange="1" MinRange="0" Name="Plant" SQLDataType="12" SourceColumn="Plant"/>
        <Column Description="Lastname" MaxRange="1" MinRange="0" Name="Process" SQLDataType="12" SourceColumn="Process"/>
        <Column Description="BorninMonth" MaxRange="1" MinRange="0" Name="ScheduleStartTimestamp" SQLDataType="93" SourceColumn="ScheduleStartTimestamp"/>
        <Column Description="BorninWeek" MaxRange="1" MinRange="0" Name="PO" SQLDataType="4" SourceColumn="PO"/>
        <Column Description="Phone" MaxRange="1" MinRange="0" Name="EquipmentName" SQLDataType="-1" SourceColumn="EquipmentName"/>
    </Columns>
    <Row>
        <FirstName>Derek</FirstName>
        <Lastname>Shah</Lastname>
        <BorninMonth>1</BorninMonth>
        <BorninWeek>1</BorninWeek>
        <Phone>925</Phone>
        <CountMonth>3</CountMonth>
    </Row>
    <Row>
        <FirstName>Michael</FirstName>
        <Lastname>Shah</Lastname>
        <BorninMonth>1</BorninMonth>
        <BorninWeek>2</BorninWeek>
        <Phone>925</Phone>
        <CountMonth>3</CountMonth>
    </Row>
    <Row>
        <FirstName>Yang</FirstName>
        <Lastname>Patel</Lastname>
        <BorninMonth>1</BorninMonth>
        <BorninWeek>1</BorninWeek>
        <Phone>925</Phone>
        <CountMonth>3</CountMonth>
    </Row>
    <Row>
        <FirstName>Bruke</FirstName>
        <Lastname>Shah</Lastname>
        <BorninMonth>1</BorninMonth>
        <BorninWeek>1</BorninWeek>
        <Phone>925</Phone>
        <CountMonth>3</CountMonth>
    </Row>
    <Row>
        <FirstName>Maulik</FirstName>
        <Lastname>Shah</Lastname>
        <BorninMonth>2</BorninMonth>
        <BorninWeek>2</BorninWeek>
        <Phone>925</Phone>
        <CountMonth>1</CountMonth>
    </Row>
    <Row>
        <FirstName>Montu</FirstName>
        <Lastname>Shah</Lastname>
        <BorninMonth>3</BorninMonth>
        <BorninWeek>1</BorninWeek>
        <Phone>925</Phone>
        <CountMonth>1</CountMonth>
    </Row>
    <Row>
        <FirstName>CHintu</FirstName>
        <Lastname>Shah</Lastname>
        <BorninMonth>4</BorninMonth>
        <BorninWeek>1</BorninWeek>
        <Phone>925</Phone>
        <CountMonth>2</CountMonth>
    </Row>
    <Row>
        <FirstName>Pintu</FirstName>
        <Lastname>Shah</Lastname>
        <BorninMonth>4</BorninMonth>
        <BorninWeek>1</BorninWeek>
        <Phone>925</Phone>
        <CountMonth>2</CountMonth>
    </Row>
    <Row>
        <FirstName>Puppy</FirstName>
        <Lastname>Patel</Lastname>
        <BorninMonth>4</BorninMonth>
        <BorninWeek>1</BorninWeek>
        <Phone>925</Phone>
        <CountMonth>2</CountMonth>
    </Row>
    <Row>
        <FirstName>Tommy</FirstName>
        <Lastname>Shah</Lastname>
        <BorninMonth>5</BorninMonth>
        <BorninWeek>1</BorninWeek>
        <Phone>925</Phone>
        <CountMonth>1</CountMonth>
    </Row>
</Rowset>

So I need some help in writing such XML which increments the value of Month and counts number of rows.

Upvotes: 1

Views: 4313

Answers (2)

Dimitre Novatchev
Dimitre Novatchev

Reputation: 243529

This transformation:

<xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform">
 <xsl:output method="text"/>
 <xsl:strip-space elements="*"/>

 <xsl:key name="kRowByNameMonth" match="Row"
  use="concat(Lastname, '+', BorninMonth)"/>

 <xsl:template match=
  "Row[Lastname='Shah']
      [generate-id()
      = generate-id(key('kRowByNameMonth',concat('Shah+',BorninMonth))[1])
      ]">
   Born in month: <xsl:value-of select="BorninMonth"/>
   <xsl:value-of select="concat(' Count: ',
                                count(key('kRowByNameMonth',
                                          concat('Shah+',BorninMonth)
                                          )
                                       )
                                )"/>
 </xsl:template>
 <xsl:template match="text()"/>
</xsl:stylesheet>

when applied on the provided XML document:

    <Rowsets DateCreated="2012-08-01T17:43:31" EndDate="2012-08-01T17:43:03" StartDate="2012-08-01T16:43:03" Version="12.0.12 Build(9)">
    <Rowset>
        <Columns>
            <Column Description="FirstName" MaxRange="1" MinRange="0" Name="Plant" SQLDataType="12" SourceColumn="Plant"/>
            <Column Description="Lastname" MaxRange="1" MinRange="0" Name="Process" SQLDataType="12" SourceColumn="Process"/>
            <Column Description="BorninMonth" MaxRange="1" MinRange="0" Name="ScheduleStartTimestamp" SQLDataType="93" SourceColumn="ScheduleStartTimestamp"/>
            <Column Description="BorninWeek" MaxRange="1" MinRange="0" Name="PO" SQLDataType="4" SourceColumn="PO"/>
            <Column Description="Phone" MaxRange="1" MinRange="0" Name="EquipmentName" SQLDataType="-1" SourceColumn="EquipmentName"/>
        </Columns>
        <Row>
            <FirstName>Derek</FirstName>
            <Lastname>Shah</Lastname>
            <BorninMonth>1</BorninMonth>
            <BorninWeek>1</BorninWeek>
            <Phone>925</Phone>
        </Row>
        <Row>
            <FirstName>Michael</FirstName>
            <Lastname>Shah</Lastname>
            <BorninMonth>1</BorninMonth>
            <BorninWeek>2</BorninWeek>
            <Phone>925</Phone>
        </Row>
        <Row>
            <FirstName>Yang</FirstName>
            <Lastname>Patel</Lastname>
            <BorninMonth>1</BorninMonth>
            <BorninWeek>1</BorninWeek>
            <Phone>925</Phone>
        </Row>
        <Row>
            <FirstName>Bruke</FirstName>
            <Lastname>Shah</Lastname>
            <BorninMonth>1</BorninMonth>
            <BorninWeek>1</BorninWeek>
            <Phone>925</Phone>
        </Row>
        <Row>
            <FirstName>Maulik</FirstName>
            <Lastname>Shah</Lastname>
            <BorninMonth>2</BorninMonth>
            <BorninWeek>2</BorninWeek>
            <Phone>925</Phone>
        </Row>
        <Row>
            <FirstName>Montu</FirstName>
            <Lastname>Shah</Lastname>
            <BorninMonth>3</BorninMonth>
            <BorninWeek>1</BorninWeek>
            <Phone>925</Phone>
        </Row>
        <Row>
            <FirstName>CHintu</FirstName>
            <Lastname>Shah</Lastname>
            <BorninMonth>4</BorninMonth>
            <BorninWeek>1</BorninWeek>
            <Phone>925</Phone>
        </Row>
        <Row>
            <FirstName>Pintu</FirstName>
            <Lastname>Shah</Lastname>
            <BorninMonth>4</BorninMonth>
            <BorninWeek>1</BorninWeek>
            <Phone>925</Phone>
        </Row>
        <Row>
            <FirstName>Puppy</FirstName>
            <Lastname>Patel</Lastname>
            <BorninMonth>4</BorninMonth>
            <BorninWeek>1</BorninWeek>
            <Phone>925</Phone>
        </Row>
        <Row>
            <FirstName>Tommy</FirstName>
            <Lastname>Shah</Lastname>
            <BorninMonth>5</BorninMonth>
            <BorninWeek>1</BorninWeek>
            <Phone>925</Phone>
        </Row>
    </Rowset>
</Rowsets>

produces the wanted, correct result:

   Born in month: 1 Count: 3
   Born in month: 2 Count: 1
   Born in month: 3 Count: 1
   Born in month: 4 Count: 2
   Born in month: 5 Count: 1

Explanation:

Proper use of the Muenchian grouping method with composite keys.

Upvotes: 1

Sean B. Durkin
Sean B. Durkin

Reputation: 12729

<xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform">
<xsl:output method="xml" indent="yes"/>
<xsl:strip-space elements="*" />

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

<xsl:template match="Row">
 <xsl:copy>
  <xsl:apply-templates select="@*|node()"/>
  <CountMonth>
    <xsl:value-of select="count(../Row
      [BorninMonth = current()/BorninMonth]
      [Lastname    = current()/Lastname   ]    
       )" /> 
  </CountMonth> 
 </xsl:copy>
</xsl:template>

</xsl:stylesheet>

Upvotes: 0

Related Questions