cancel
Turn on suggestions
Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
Showing results for 
Search instead for 
Did you mean: 

How to filter record having particular field value and remove duplicate using XSLT code

Go to solution
Nikhil_Gursal
Participant

on ‎07-31-2023 6:56 AM

0 Kudos

Hi Community,

I'm working on scenario where input data will be having multiple records for same user but i need the record who's event value = 123 (Field event value ) and other records as it is if they are unique.

Input Data - Having two records for user id 1066 out of which one has event value 123 and other 7859 , here we need only record which has event value 123 and last record which is unique for user id 1067.

<EmpJob>
	<EmpJob>
		<emplStatus>123</emplStatus>
		<userNav>
			<User>
				<empInfo>
					<EmpEmployment>
						<prevEmployeeId/>
					</EmpEmployment>
				</empInfo>
			</User>
		</userNav>
		<employmentType>456</employmentType>
		<customString2Nav>
			<cust_band>
				<externalName_defaultValue>abc</externalName_defaultValue>
			</cust_band>
		</customString2Nav>
		<costCenter>def</costCenter>
		<jobTitle>hij</jobTitle>
		<company>klm</company>
		<employmentNav>
			<EmpEmployment>
				<originalStartDate>2016-11-07T00:00:00.000</originalStartDate>
				<personNav>
					<PerPerson>
						<personalInfoNav>
							<PerPersonal>
								<firstName>a</firstName>
								<lastName>b</lastName>
								<gender>F</gender>
								<nationality>d</nationality>
								<initials>B</initials>
								<salutation>567</salutation>
								<maritalStatus>7898</maritalStatus>
							</PerPersonal>
						</personalInfoNav>
						<nationalIdNav>
							<PerNationalId>
								<country>in</country>
								<nationalId>2345</nationalId>
								<cardType>jhf</cardType>
							</PerNationalId>
						</nationalIdNav>
						<phoneNav>
							<PerPhone>
								<phoneType>235</phoneType>
								<phoneNumber>678</phoneNumber>
							</PerPhone>
							<PerPhone>
								<phoneType>890</phoneType>
								<phoneNumber>3456</phoneNumber>
							</PerPhone>
						</phoneNav>
						<emailNav>
							<PerEmail>
								<emailAddress>dummy@test.com</emailAddress>
								<emailType>6789</emailType>
							</PerEmail>
						</emailNav>
						<dateOfBirth>1990-02-02T00:00:00.000</dateOfBirth>
					</PerPerson>
				</personNav>
				<endDate/>
				<compInfoNav>
					<EmpCompensation>
						<empPayCompRecurringNav>
							<EmpPayCompRecurring>
								<payComponent>zxcv</payComponent>
								<paycompvalue>678</paycompvalue>
							</EmpPayCompRecurring>
							<EmpPayCompRecurring>
								<payComponent>mnbv</payComponent>
								<paycompvalue>8765</paycompvalue>
							</EmpPayCompRecurring>
						</empPayCompRecurringNav>
					</EmpCompensation>
				</compInfoNav>
				<paymentInformationNav/>
				<firstDateWorked>2016-11-07T00:00:00.000</firstDateWorked>
			</EmpEmployment>
		</employmentNav>
		<event>123</event>
		<userId>1066</userId>
		<eventReason>Key</eventReason>
                <startDate>2022-10-01T00:00:00.000</startDate>
	</EmpJob>
	<EmpJob>
		<emplStatus>123</emplStatus>
		<userNav>
			<User>
				<empInfo>
					<EmpEmployment>
						<prevEmployeeId/>
					</EmpEmployment>
				</empInfo>
			</User>
		</userNav>
		<employmentType>456</employmentType>
		<customString2Nav>
			<cust_band>
				<externalName_defaultValue>abc</externalName_defaultValue>
			</cust_band>
		</customString2Nav>
		<costCenter>def</costCenter>
		<jobTitle>hij</jobTitle>
		<company>klm</company>
		<employmentNav>
			<EmpEmployment>
				<originalStartDate>2016-11-07T00:00:00.000</originalStartDate>
				<personNav>
					<PerPerson>
						<personalInfoNav>
							<PerPersonal>
								<firstName>a</firstName>
								<lastName>b</lastName>
								<gender>F</gender>
								<nationality>d</nationality>
								<initials>B</initials>
								<salutation>567</salutation>
								<maritalStatus>7898</maritalStatus>
							</PerPersonal>
						</personalInfoNav>
						<nationalIdNav>
							<PerNationalId>
								<country>in</country>
								<nationalId>2345</nationalId>
								<cardType>jhf</cardType>
							</PerNationalId>
						</nationalIdNav>
						<phoneNav>
							<PerPhone>
								<phoneType>235</phoneType>
								<phoneNumber>678</phoneNumber>
							</PerPhone>
							<PerPhone>
								<phoneType>890</phoneType>
								<phoneNumber>3456</phoneNumber>
							</PerPhone>
						</phoneNav>
						<emailNav>
							<PerEmail>
								<emailAddress>dummy@test.com</emailAddress>
								<emailType>6789</emailType>
							</PerEmail>
						</emailNav>
						<dateOfBirth>1990-02-02T00:00:00.000</dateOfBirth>
					</PerPerson>
				</personNav>
				<endDate/>
				<compInfoNav>
					<EmpCompensation>
						<empPayCompRecurringNav>
							<EmpPayCompRecurring>
								<payComponent>zxcv</payComponent>
								<paycompvalue>678</paycompvalue>
							</EmpPayCompRecurring>
							<EmpPayCompRecurring>
								<payComponent>mnbv</payComponent>
								<paycompvalue>8765</paycompvalue>
							</EmpPayCompRecurring>
						</empPayCompRecurringNav>
					</EmpCompensation>
				</compInfoNav>
				<paymentInformationNav/>
				<firstDateWorked>2016-11-07T00:00:00.000</firstDateWorked>
			</EmpEmployment>
		</employmentNav>
		<event>7859</event>
		<userId>1066</userId>
		<eventReason>Key</eventReason>
                <startDate>2022-10-01T00:00:00.000</startDate>

	</EmpJob>
	<EmpJob>
		<emplStatus>123</emplStatus>
		<userNav>
			<User>
				<empInfo>
					<EmpEmployment>
						<prevEmployeeId/>
					</EmpEmployment>
				</empInfo>
			</User>
		</userNav>
		<employmentType>456</employmentType>
		<customString2Nav>
			<cust_band>
				<externalName_defaultValue>abc</externalName_defaultValue>
			</cust_band>
		</customString2Nav>
		<costCenter>def</costCenter>
		<jobTitle>hij</jobTitle>
		<company>klm</company>
		<employmentNav>
			<EmpEmployment>
				<originalStartDate>2016-11-07T00:00:00.000</originalStartDate>
				<personNav>
					<PerPerson>
						<personalInfoNav>
							<PerPersonal>
								<firstName>a</firstName>
								<lastName>b</lastName>
								<gender>F</gender>
								<nationality>d</nationality>
								<initials>B</initials>
								<salutation>567</salutation>
								<maritalStatus>7898</maritalStatus>
							</PerPersonal>
						</personalInfoNav>
						<nationalIdNav>
							<PerNationalId>
								<country>in</country>
								<nationalId>2345</nationalId>
								<cardType>jhf</cardType>
							</PerNationalId>
						</nationalIdNav>
						<phoneNav>
							<PerPhone>
								<phoneType>235</phoneType>
								<phoneNumber>678</phoneNumber>
							</PerPhone>
							<PerPhone>
								<phoneType>890</phoneType>
								<phoneNumber>3456</phoneNumber>
							</PerPhone>
						</phoneNav>
						<emailNav>
							<PerEmail>
								<emailAddress>dummy@test.com</emailAddress>
								<emailType>6789</emailType>
							</PerEmail>
						</emailNav>
						<dateOfBirth>1990-02-02T00:00:00.000</dateOfBirth>
					</PerPerson>
				</personNav>
				<endDate/>
				<compInfoNav>
					<EmpCompensation>
						<empPayCompRecurringNav>
							<EmpPayCompRecurring>
								<payComponent>zxcv</payComponent>
								<paycompvalue>678</paycompvalue>
							</EmpPayCompRecurring>
							<EmpPayCompRecurring>
								<payComponent>mnbv</payComponent>
								<paycompvalue>8765</paycompvalue>
							</EmpPayCompRecurring>
						</empPayCompRecurringNav>
					</EmpCompensation>
				</compInfoNav>
				<paymentInformationNav/>
				<firstDateWorked>2016-11-07T00:00:00.000</firstDateWorked>
			</EmpEmployment>
		</employmentNav>
		<event>1580</event>
		<userId>1067</userId>
		<eventReason>Key</eventReason>
                <startDate>2022-10-01T00:00:00.000</startDate>

	</EmpJob>
</EmpJob><br>

Output should be as per below :

<EmpJob>
	<EmpJob>
		<emplStatus>123</emplStatus>
		<userNav>
			<User>
				<empInfo>
					<EmpEmployment>
						<prevEmployeeId/>
					</EmpEmployment>
				</empInfo>
			</User>
		</userNav>
		<employmentType>456</employmentType>
		<customString2Nav>
			<cust_band>
				<externalName_defaultValue>abc</externalName_defaultValue>
			</cust_band>
		</customString2Nav>
		<costCenter>def</costCenter>
		<jobTitle>hij</jobTitle>
		<company>klm</company>
		<employmentNav>
			<EmpEmployment>
				<originalStartDate>2016-11-07T00:00:00.000</originalStartDate>
				<personNav>
					<PerPerson>
						<personalInfoNav>
							<PerPersonal>
								<firstName>a</firstName>
								<lastName>b</lastName>
								<gender>F</gender>
								<nationality>d</nationality>
								<initials>B</initials>
								<salutation>567</salutation>
								<maritalStatus>7898</maritalStatus>
							</PerPersonal>
						</personalInfoNav>
						<nationalIdNav>
							<PerNationalId>
								<country>in</country>
								<nationalId>2345</nationalId>
								<cardType>jhf</cardType>
							</PerNationalId>
						</nationalIdNav>
						<phoneNav>
							<PerPhone>
								<phoneType>235</phoneType>
								<phoneNumber>678</phoneNumber>
							</PerPhone>
							<PerPhone>
								<phoneType>890</phoneType>
								<phoneNumber>3456</phoneNumber>
							</PerPhone>
						</phoneNav>
						<emailNav>
							<PerEmail>
								<emailAddress>dummy@test.com</emailAddress>
								<emailType>6789</emailType>
							</PerEmail>
						</emailNav>
						<dateOfBirth>1990-02-02T00:00:00.000</dateOfBirth>
					</PerPerson>
				</personNav>
				<endDate/>
				<compInfoNav>
					<EmpCompensation>
						<empPayCompRecurringNav>
							<EmpPayCompRecurring>
								<payComponent>zxcv</payComponent>
								<paycompvalue>678</paycompvalue>
							</EmpPayCompRecurring>
							<EmpPayCompRecurring>
								<payComponent>mnbv</payComponent>
								<paycompvalue>8765</paycompvalue>
							</EmpPayCompRecurring>
						</empPayCompRecurringNav>
					</EmpCompensation>
				</compInfoNav>
				<paymentInformationNav/>
				<firstDateWorked>2016-11-07T00:00:00.000</firstDateWorked>
			</EmpEmployment>
		</employmentNav>
		<event>123</event>
		<userId>1066</userId>
		<eventReason>Key</eventReason>
                <startDate>2022-10-01T00:00:00.000</startDate>

	</EmpJob>
	<EmpJob>
		<emplStatus>123</emplStatus>
		<userNav>
			<User>
				<empInfo>
					<EmpEmployment>
						<prevEmployeeId/>
					</EmpEmployment>
				</empInfo>
			</User>
		</userNav>
		<employmentType>456</employmentType>
		<customString2Nav>
			<cust_band>
				<externalName_defaultValue>abc</externalName_defaultValue>
			</cust_band>
		</customString2Nav>
		<costCenter>def</costCenter>
		<jobTitle>hij</jobTitle>
		<company>klm</company>
		<employmentNav>
			<EmpEmployment>
				<originalStartDate>2016-11-07T00:00:00.000</originalStartDate>
				<personNav>
					<PerPerson>
						<personalInfoNav>
							<PerPersonal>
								<firstName>a</firstName>
								<lastName>b</lastName>
								<gender>F</gender>
								<nationality>d</nationality>
								<initials>B</initials>
								<salutation>567</salutation>
								<maritalStatus>7898</maritalStatus>
							</PerPersonal>
						</personalInfoNav>
						<nationalIdNav>
							<PerNationalId>
								<country>in</country>
								<nationalId>2345</nationalId>
								<cardType>jhf</cardType>
							</PerNationalId>
						</nationalIdNav>
						<phoneNav>
							<PerPhone>
								<phoneType>235</phoneType>
								<phoneNumber>678</phoneNumber>
							</PerPhone>
							<PerPhone>
								<phoneType>890</phoneType>
								<phoneNumber>3456</phoneNumber>
							</PerPhone>
						</phoneNav>
						<emailNav>
							<PerEmail>
								<emailAddress>dummy@test.com</emailAddress>
								<emailType>6789</emailType>
							</PerEmail>
						</emailNav>
						<dateOfBirth>1990-02-02T00:00:00.000</dateOfBirth>
					</PerPerson>
				</personNav>
				<endDate/>
				<compInfoNav>
					<EmpCompensation>
						<empPayCompRecurringNav>
							<EmpPayCompRecurring>
								<payComponent>zxcv</payComponent>
								<paycompvalue>678</paycompvalue>
							</EmpPayCompRecurring>
							<EmpPayCompRecurring>
								<payComponent>mnbv</payComponent>
								<paycompvalue>8765</paycompvalue>
							</EmpPayCompRecurring>
						</empPayCompRecurringNav>
					</EmpCompensation>
				</compInfoNav>
				<paymentInformationNav/>
				<firstDateWorked>2016-11-07T00:00:00.000</firstDateWorked>
			</EmpEmployment>
		</employmentNav>
		<event>1580</event>
		<userId>1067</userId>
		<eventReason>Key</eventReason>
                <startDate>2022-10-01T00:00:00.000</startDate>

	</EmpJob>
</EmpJob><br>

I tried using below XSLT code but not getting expected result , not sure if doing any step wrong, any help will be appreciated.

<xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform">
	<xsl:key name="userByIdAndEvent" match="EmpJob[event = '123']" use="userId"/>
	<xsl:template match="EmpJob">
		<xsl:if test="generate-id() = generate-id(key('userByIdAndEvent', userId)[1])">
			<xsl:copy>
				<xsl:apply-templates select="@*|node()"/>
			</xsl:copy>
		</xsl:if>
	</xsl:template>
	<xsl:template match="@*|node()">
		<xsl:copy>
			<xsl:apply-templates select="@*|node()"/>
		</xsl:copy>
	</xsl:template>
</xsl:stylesheet>

Thanks,

Nikhil

Show replies
Show replies
Answer
View Entire Topic
Ryan-Crosby
Active Contributor
‎08-02-2023 2:08 PM
0 Kudos

Hi Nikhil,

The first issue with the XSLT that you have is that it matches against EmpJob but the repeating EmpJob nodes are embedded in a top-level EmpJob node - should really be EmpJobs for clarity and to denote it's a list. The second is that the generated key is blank for EmpJob nodes where no event 123 exists for a user (there is a key for duplicate nodes but that value is different than the one for the node with 123 despite the match picking only 123). Given that information I adjusted the key designation slightly, and matched against the top-level EmpJob with a for-each-group and an extended if qualification. See below for an XSLT that should work:

<xsl:stylesheet version="3.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform">
  <xsl:key name="userByIdAndEvent" match="EmpJob/EmpJob[event = '123']" use="userId"/>
  <xsl:output method="xml" version="1.0" encoding="UTF-8" indent="yes"/>
  <xsl:mode on-no-match="shallow-copy"/>
  <xsl:template match="EmpJob">
    <EmpJob>
      <xsl:for-each-group select="EmpJob" group-by="userId">
        <xsl:for-each-group select="current-group()" group-by="startDate">
          <xsl:sort select="startDate" order="ascending"/>
          <xsl:choose>
            <xsl:when test="generate-id() = generate-id(key('userByIdAndEvent', userId))"><xsl:copy><xsl:apply-templates select="current-group()[1]/*"/></xsl:copy></xsl:when>
            <xsl:when test="generate-id(key('userByIdAndEvent', userId)) = '' and event != '123' and position() = last()"><xsl:copy><xsl:apply-templates select="current-group()/*"/></xsl:copy></xsl:when>
          </xsl:choose>
        </xsl:for-each-group>
      </xsl:for-each-group>
    </EmpJob>
  </xsl:template>
</xsl:stylesheet>

Regards,

Ryan Crosby

Show replies
Show replies
Nikhil_Gursal
Participant
‎08-03-2023 10:03 AM
0 Kudos

Hi ryan.crosby ,

Thanks for details explanation and understood where was wrong,the code is working perfect .

Just want to clarify one more thing , Is it possible to add one more validation for field startDate . I mean if we have 3 records with same user id (event is not equal to 123) but on the basis of startDate field i want record which has latest start date and other two should not considered for this.

I was trying to fit this in above code in or condition using below but not working.

(generate-id(key('userByIdAndEvent', userId)) = '' and select =max(//startDate/xs:date(.))" >

Thanks,

Nikhil

Ryan-Crosby
Active Contributor
‎08-03-2023 12:41 PM
0 Kudos

nikhil.85 if you post a sample XML showing what you are referring to and your expected output I can dabble with it.

Nikhil_Gursal
Participant
‎08-03-2023 1:13 PM
0 Kudos

Hi ryan.crosby ,

Attached is the input file : Here 4th and 5th record is same only difference is startDate field value , need only record which has latest start date (in this scenario 2023-10-01T00:00

Please refer attached input and output expected.

Thanks in advancexslt-input.txtxslt-output.txt

Ryan-Crosby
Active Contributor
‎08-03-2023 3:21 PM
0 Kudos

nikhil.85 I have updated the answer with an XSLT that uses grouping and sorting to get the newest record.

Nikhil_Gursal
Participant
‎08-04-2023 5:50 AM
0 Kudos

Thanks ryan.crosby

New logic is working as expected but it disturb logic for first condition where we need remove duplicate record and select record which has event value 123. Post testing i can see its merging first two record for user 1066.

Can you please suggest any resources available where i can refer XSLT syntax and understand its concept.

Thanks,

Nikhil

Ryan-Crosby
Active Contributor
‎08-04-2023 1:22 PM
0 Kudos

nikhil.85 I had to change the approach because the event requirement needed one tactic while the date handling required another. Please see the update code block. Regarding XSLT learning resources... I used xsl:sort and xsl:for-each-group for the first time yesterday simply by scouring the internet with Google, so I can't truly recommend any reading/learning for XSLT.

Ask a Question