CODEWITHSUNDEEP
xsltxslt-2.0
abishek rao
abishek rao

Reputation: 11

XSLT Help - Remove duplicate XML nodes based on two conditions

I need to remove duplicate nodes from xml using the following two keys:

  1. Delete row/node IF [Assignment Number + Legal Entity + Effective Date] is duplicated.
  2. Delete row/node IF [Assignment Number + Legal Entity + Payroll Definition + Time Card Required + Overtime Period] value is the same as previous row ONLY. Do not exclude if the value exists in the historical row but not previous row.

The xml is sorted by effective date. My xslt solves #2 using preceding-sibline node function and remove duplicates. Before it runs through this logic, It needs to first delete duplicate entries of <wd:Worker_BP_Events>for each <wd:Report_Entry> if the combination of Assignment Number+Legal Entity+Eff date are same.

xml:

<wd:Report_Data xmlns:wd="urn:com.workday.report/ERP-PAY-CR-PAYROLL_RELATIONSHIPS">
    <wd:Report_Entry>
        <wd:Worker_BP_Events>
            <wd:COUNTRY_CODE>US</wd:COUNTRY_CODE>
            <wd:UNIQUE_IDENTIFIER>2250403</wd:UNIQUE_IDENTIFIER>
            <wd:ASSIGNMENT_NUMBER>2250403-06202016A14</wd:ASSIGNMENT_NUMBER>
            <wd:LEGISLATIVE_DATA_GROUP>US Care LDG</wd:LEGISLATIVE_DATA_GROUP>
            <wd:LEGAL_ENTITY>A14</wd:LEGAL_ENTITY>
            <wd:EFFECTIVE_START_DATE>2018-12-23</wd:EFFECTIVE_START_DATE>
            <wd:PAYROLL_DEFINITION wd:Descriptor="Biweekly A"> </wd:PAYROLL_DEFINITION>
            <wd:TIMECARD_REQUIRED_FLAG>Y</wd:TIMECARD_REQUIRED_FLAG>
            <wd:OVERTIME_PERIOD wd:Descriptor="TD 7 Days"> </wd:OVERTIME_PERIOD>
            <wd:START_DATE>2018-12-23</wd:START_DATE>

        </wd:Worker_BP_Events>
        <wd:Worker_BP_Events>
            <wd:COUNTRY_CODE>US</wd:COUNTRY_CODE>
            <wd:UNIQUE_IDENTIFIER>2250403</wd:UNIQUE_IDENTIFIER>
            <wd:ASSIGNMENT_NUMBER>2250403-06202016A14</wd:ASSIGNMENT_NUMBER>
            <wd:LEGISLATIVE_DATA_GROUP>US Care LDG</wd:LEGISLATIVE_DATA_GROUP>
            <wd:LEGAL_ENTITY>A14</wd:LEGAL_ENTITY>
            <wd:EFFECTIVE_START_DATE>2019-06-23</wd:EFFECTIVE_START_DATE>
            <wd:PAYROLL_DEFINITION wd:Descriptor="Biweekly A"> </wd:PAYROLL_DEFINITION>
            <wd:TIMECARD_REQUIRED_FLAG>Y</wd:TIMECARD_REQUIRED_FLAG>
            <wd:OVERTIME_PERIOD wd:Descriptor="TD 7 Days"> </wd:OVERTIME_PERIOD>
            <wd:START_DATE>2019-06-23</wd:START_DATE>

        </wd:Worker_BP_Events>
        <wd:Worker_BP_Events>
            <wd:COUNTRY_CODE>US</wd:COUNTRY_CODE>
            <wd:UNIQUE_IDENTIFIER>2250403</wd:UNIQUE_IDENTIFIER>
            <wd:ASSIGNMENT_NUMBER>2250403-06202016A14</wd:ASSIGNMENT_NUMBER>
            <wd:LEGISLATIVE_DATA_GROUP>US Care LDG</wd:LEGISLATIVE_DATA_GROUP>
            <wd:LEGAL_ENTITY>A14</wd:LEGAL_ENTITY>
            <wd:EFFECTIVE_START_DATE>2019-06-23</wd:EFFECTIVE_START_DATE>
            <wd:PAYROLL_DEFINITION wd:Descriptor="Biweekly A"> </wd:PAYROLL_DEFINITION>
            <wd:TIMECARD_REQUIRED_FLAG>Y</wd:TIMECARD_REQUIRED_FLAG>
            <wd:OVERTIME_PERIOD wd:Descriptor="TD 14 Days"> </wd:OVERTIME_PERIOD>
            <wd:START_DATE>2019-06-23</wd:START_DATE>

        </wd:Worker_BP_Events>
        <wd:Worker_BP_Events>
            <wd:COUNTRY_CODE>US</wd:COUNTRY_CODE>
            <wd:UNIQUE_IDENTIFIER>2250403</wd:UNIQUE_IDENTIFIER>
            <wd:ASSIGNMENT_NUMBER>2250403-06202016A09</wd:ASSIGNMENT_NUMBER>
            <wd:LEGISLATIVE_DATA_GROUP>US Care LDG</wd:LEGISLATIVE_DATA_GROUP>
            <wd:LEGAL_ENTITY>A09</wd:LEGAL_ENTITY>
            <wd:EFFECTIVE_START_DATE>2020-03-15</wd:EFFECTIVE_START_DATE>
            <wd:PAYROLL_DEFINITION wd:Descriptor="Biweekly A"> </wd:PAYROLL_DEFINITION>
            <wd:TIMECARD_REQUIRED_FLAG>Y</wd:TIMECARD_REQUIRED_FLAG>
            <wd:OVERTIME_PERIOD wd:Descriptor="TD 7 Days"> </wd:OVERTIME_PERIOD>
            <wd:START_DATE>2020-03-15</wd:START_DATE>

        </wd:Worker_BP_Events>

        <wd:Worker>
            <wd:EFFECTIVE_END_DATE>4712-12-31</wd:EFFECTIVE_END_DATE>
            <wd:CLOSE_DATE>4712-12-31</wd:CLOSE_DATE>
        </wd:Worker>
    </wd:Report_Entry>
</wd:Report_Data>

xslt:

<xsl:stylesheet xmlns:xsl="http://www.w3.org/1999/XSL/Transform"
    xmlns:xs="http://www.w3.org/2001/XMLSchema"
    exclude-result-prefixes="xs"
    xmlns:wd="urn:com.workday.report/ERP-PAY-CR-PAYROLL_RELATIONSHIPS"
    version="2.0">
    <xsl:output method="text"/>
    <xsl:variable name="linefeed" select="'&#xD;&#xA;'"/>
    <xsl:variable name="pipe" select="'|'"/>

    
    <xsl:param name="quote">"</xsl:param>
    <xsl:template match="/">


        <!--  File Detail Layout  -->
        <xsl:for-each select="wd:Report_Data/wd:Report_Entry/wd:Worker_BP_Events[not(concat(wd:ASSIGNMENT_NUMBER,wd:LEGAL_ENTITY,wd:PAYROLL_DEFINITION/@wd:Descriptor,wd:TIMECARD_REQUIRED_FLAG,wd:OVERTIME_PERIOD/@wd:Descriptor)=preceding-sibling::*[1]/concat(wd:ASSIGNMENT_NUMBER,wd:LEGAL_ENTITY,wd:PAYROLL_DEFINITION/@wd:Descriptor,wd:TIMECARD_REQUIRED_FLAG,wd:OVERTIME_PERIOD/@wd:Descriptor))]">
            <xsl:call-template name="Write-Detail-Record"/>
        </xsl:for-each>
    </xsl:template>
    
        

    <xsl:template name="Write-Detail-Record">

        
        <xsl:value-of select="wd:COUNTRY_CODE"/>
        <xsl:value-of select="$pipe"/>
        
        <xsl:value-of select="wd:UNIQUE_IDENTIFIER"/>
        <xsl:value-of select="$pipe"/>
        
        <xsl:value-of select="wd:ASSIGNMENT_NUMBER"/>
        <xsl:value-of select="$pipe"/>
        
        <xsl:value-of select="wd:LEGISLATIVE_DATA_GROUP"/>
        <xsl:value-of select="$pipe"/>
        
        
        <xsl:value-of select="wd:LEGAL_ENTITY"/>
        <xsl:value-of select="$pipe"/>
        
        
        <xsl:value-of select="format-date(wd:EFFECTIVE_START_DATE,'[Y0001]-[M01]-[D01]')"/>
        <xsl:value-of select="$pipe"/>
        
        <xsl:value-of select="format-date(../wd:Worker/wd:EFFECTIVE_END_DATE,'[Y0001]-[M01]-[D01]')"/>
        <xsl:value-of select="$pipe"/>
        
        
        <xsl:value-of select="wd:PAYROLL_DEFINITION/@wd:Descriptor"/>
        <xsl:value-of select="$pipe"/>
        
        <xsl:value-of select="wd:TIMECARD_REQUIRED_FLAG"/>
        <xsl:value-of select="$pipe"/>
        
        <xsl:value-of select="wd:OVERTIME_PERIOD/@wd:Descriptor"/>
        <xsl:value-of select="$pipe"/>
        
        <xsl:value-of select="format-date(wd:START_DATE,'[Y0001]-[M01]-[D01]')"/>
        <xsl:value-of select="$pipe"/>
        
        <xsl:value-of select="format-date(../wd:Worker/wd:CLOSE_DATE,'[Y0001]-[M01]-[D01]')"/>
        <xsl:value-of select="$pipe"/>
        
        
        <xsl:value-of select="format-date(wd:FINAL_CLOSE_DATE,'[Y0001]-[M01]-[D01]')"/>
        <xsl:value-of select="$pipe"/>
        
        <xsl:value-of select="format-date(../wd:Worker/wd:LAST_STANDARD_PROCESS_DATE,'[Y0001]-[M01]-[D01]')"/>
        
        
        
        <xsl:value-of select="$linefeed"/>
        
        
    </xsl:template>
</xsl:stylesheet>

Upvotes: 0

Views: 231

Answers (1)

Martin Honnen
Martin Honnen

Reputation: 167506

Use for-each-group

<xsl:stylesheet xmlns:xsl="http://www.w3.org/1999/XSL/Transform"
    xmlns:xs="http://www.w3.org/2001/XMLSchema"
    xmlns:wd="urn:com.workday.report/ERP-PAY-CR-PAYROLL_RELATIONSHIPS"
    xpath-default-namespace="urn:com.workday.report/ERP-PAY-CR-PAYROLL_RELATIONSHIPS"
    exclude-result-prefixes="#all"
    version="3.0">

  <xsl:output indent="yes"/>
  
  <xsl:mode on-no-match="shallow-copy"/>
  
  <xsl:template match="Report_Date">
    <xsl:copy>
      <xsl:variable name="unique-entries" as="element(Report_Entry)*">
        <xsl:for-each-group select="Report_Entry" composite="yes" group-by="ASSIGNMENT_NUMBER, LEGAL_ENTITY, EFFECTIVE_START_DATE">
          <xsl:sequence select="."/>
        </xsl:for-each-group>        
      </xsl:variable>
      <xsl:for-each-group select="$unique-entries" composite="yes" group-adjacent="ASSIGNMENT_NUMBER, LEGAL_ENTITY, PAYROLL_DEFINITION/@wd:Descriptor, TIMECARD_REQUIRED_FLAG, OVERTIME_PERIOD/@wd:Descriptor">
        <xsl:sequence select="."/>
      </xsl:for-each-group>
    </xsl:copy>
  </xsl:template>
  
</xsl:stylesheet>

Upvotes: 1

Related Questions