Skip to Content
0
Jul 31, 2023 at 05:56 AM

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

223 Views Last edit Aug 03, 2023 at 09:05 AM 3 rev

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