CODEWITHSUNDEEP
xmlxslt-1.0xslt-2.0
Abhi
Abhi

Reputation: 1

How to do a sum from xml based on condition using xslt code

I need some help to add the quantity using xslt code - whenever time off date is less than compensation effective date add quantity values which would be 36 in below example.

Please see xml example.

<?xml version="1.0" encoding="UTF-8"?>
<pi:Payroll_Extract_Employees xmlns:pi="urn:com.workday/picof">
    <pi:PayGroup>
        <pi:Header>
            <pi:Version>24</pi:Version>

        </pi:Header>
        <pi:Employee>
            <pi:Summary>
                <pi:Employee_ID>123</pi:Employee_ID>
            </pi:Summary>
            <pi:Personal>
                <pi:First_Name>test</pi:First_Name>
                <pi:Last_Name>last anme</pi:Last_Name>

            </pi:Personal>
            <pi:Position>
                <pi:Operation>ADD</pi:Operation>
                <pi:Compensation_Effective_Date pi:PriorValue=""
                    >20161212</pi:Compensation_Effective_Date>

            </pi:Position>
            <pi:Position>
                <pi:Operation>REMOVE</pi:Operation>
                 <pi:Compensation_Effective_Date>20160401</pi:Compensation_Effective_Date>
            </pi:Position>
            <pi:Time_Off>
                <pi:Code pi:PriorValue="">CHN_PERSONAL_PLAN</pi:Code>
                <pi:Time_Off_Date pi:PriorValue="">20161122</pi:Time_Off_Date>
                <pi:Quantity pi:PriorValue="">8</pi:Quantity>
                <pi:Unit_of_Time pi:PriorValue="">HOURS</pi:Unit_of_Time>
            </pi:Time_Off>
            <pi:Time_Off>
                 <pi:Code pi:PriorValue="">CHN_PERSONAL_PLAN</pi:Code>
                 <pi:Time_Off_Date pi:PriorValue="">20161123</pi:Time_Off_Date>
                <pi:Quantity pi:PriorValue="">8</pi:Quantity>
                <pi:Unit_of_Time pi:PriorValue="">HOURS</pi:Unit_of_Time>
            </pi:Time_Off>
            <pi:Time_Off>
                <pi:Code pi:PriorValue="">CHN_PERSONAL_PLAN</pi:Code>
                <pi:Time_Off_Date pi:PriorValue="">20161211</pi:Time_Off_Date>
                <pi:Quantity pi:PriorValue="">8</pi:Quantity>
                <pi:Unit_of_Time pi:PriorValue="">HOURS</pi:Unit_of_Time>
            </pi:Time_Off>
            <pi:Time_Off>
                <pi:Code pi:PriorValue="">CHN_PERSONAL_PLAN</pi:Code>
                <pi:Time_Off_Date pi:PriorValue="">20161212</pi:Time_Off_Date>
                <pi:Quantity pi:PriorValue="">8</pi:Quantity>
                <pi:Unit_of_Time pi:PriorValue="">HOURS</pi:Unit_of_Time>
            </pi:Time_Off>
            <pi:Time_Off>
                <pi:Time_Off_Type pi:PriorValue="">TOT_Personal_Leave_Hours</pi:Time_Off_Type>
                <pi:Time_Off_Date pi:PriorValue="">20161213</pi:Time_Off_Date>
                <pi:Quantity pi:PriorValue="">8</pi:Quantity>
                <pi:Unit_of_Time pi:PriorValue="">HOURS</pi:Unit_of_Time>
            </pi:Time_Off>
            <pi:Time_Off>
                <pi:Code pi:PriorValue="">CHN_PERSONAL_PLAN</pi:Code>
                <pi:Time_Off_Date pi:PriorValue="">20161214</pi:Time_Off_Date>
                <pi:Quantity pi:PriorValue="">8</pi:Quantity>
                <pi:Unit_of_Time pi:PriorValue="">HOURS</pi:Unit_of_Time>
            </pi:Time_Off>
               </pi:Employee>
    </pi:PayGroup>
</pi:Payroll_Extract_Employees>`

Here is a sample xslt code I tried, but it seems to be adding all the quantity for the particular time off code

   <xsl:choose>
                                        <xsl:when test="pi:Position/pi:Total_Annual_Base_Pay[exists(@pi:PriorValue)]
                                            or pi:Position/pi:Compensation_Effective_Date[exists(@pi:PriorValue)]          ">
                                          <xsl:if test="pi:Time_Off[pi:Code='CHN_PERSONAL_PLAN']/pi:Time_Off_Date &lt; pi:Position[pi:Operation!='REMOVE']/pi:Compensation_Effective_Date ">         
                                                <xsl:value-of
                                                    select="sum(pi:Time_Off[pi:Code='CHN_PERSONAL_PLAN']/pi:Quantity)
                                                    + sum(pi:Time_Off_Correction[pi:Code='CHN_PERSONAL_PLAN']/pi:Quantity)"
                                                />
                                            </xsl:if>
                                        </xsl:when>
                                    <!--   <xsl:otherwise>
                                         <xsl:value-of
                                             select="sum(pi:Time_Off[pi:Code='CHN_PERSONAL_PLAN']/pi:Quantity)
                                             + sum(pi:Time_Off_Correction[pi:Code='CHN_PERSONAL_PLAN']/pi:Quantity)"
                                         />
                                     </xsl:otherwise>-->
                                    </xsl:choose>

Upvotes: 0

Views: 164

Answers (1)

michael.hor257k
michael.hor257k

Reputation: 117140

whenever time off date is less than compensation effective date add quantity values which would be 36

I believe this satisfies the stated condition:

XSLT 1.0

<xsl:stylesheet version="1.0" 
xmlns:xsl="http://www.w3.org/1999/XSL/Transform"
xmlns:pi="urn:com.workday/picof"
exclude-result-prefixes="pi">
<xsl:output method="xml" version="1.0" encoding="UTF-8" indent="yes"/>
<xsl:strip-space elements="*"/>

<xsl:template match="/pi:Payroll_Extract_Employees">
    <result>
        <xsl:value-of select="sum(pi:PayGroup/pi:Employee/pi:Time_Off[pi:Time_Off_Date &lt; ancestor::pi:Employee/pi:Position/pi:Compensation_Effective_Date]/pi:Quantity)"/>
    </result>
</xsl:template>

</xsl:stylesheet>

However, when applied to your input example, the result is 24, not 36.

Upvotes: 1

Related Questions