Search code examples
xmlxpathxquerybi-publisher

Find the Pay Begin Date associated with the minimum Pay Period

I am working with BI Publisher Desktop and using data from Oracle PeopleSoft.

What I need to do: Find the minimum E.PAY_PERIOD and show the E.PAY_BEGIN_DT associated with this pay period.

I have an XML file that looks similar to this:

<row rownumber="535">
    <C.OTH_HRS>-100.0</C.OTH_HRS>
    <C.OTH_EARNS>-1000.00</C.OTH_EARNS>
    <C.ERNCD><![CDATA[WCB]]></C.ERNCD>
    <D.DESCR><![CDATA[Workers Compensation]]></D.DESCR>
    <B.PAY_END_DT>2017-08-29</B.PAY_END_DT>
    <E.PAY_BEGIN_DT>2017-08-16</E.PAY_BEGIN_DT>
    <E.PAY_END_DT>2017-08-29</E.PAY_END_DT>
    <B.EMPLID><![CDATA[99999]]></B.EMPLID>
    <B.NAME><![CDATA[LastName,FirstName]]></B.NAME>
    <F.UNION_CD><![CDATA[7]]></F.UNION_CD>
    <E.PAY_PERIOD><![CDATA[3]]></E.PAY_PERIOD>
</row>

<row rownumber="536">
    <C.OTH_HRS>0</C.OTH_HRS>
    <C.OTH_EARNS>-10.00</C.OTH_EARNS>
    <C.ERNCD><![CDATA[052]]></C.ERNCD>
    <D.DESCR><![CDATA[Adjustment to sick pay (15%)]]></D.DESCR>
    <B.PAY_END_DT>2017-09-12</B.PAY_END_DT>
    <E.PAY_BEGIN_DT>2017-08-30</E.PAY_BEGIN_DT>
    <E.PAY_END_DT>2017-09-12</E.PAY_END_DT>
    <B.EMPLID><![CDATA[99998]]></B.EMPLID>
    <B.NAME><![CDATA[LastName,FirstName]]></B.NAME>
    <F.UNION_CD><![CDATA[7]]></F.UNION_CD>
    <E.PAY_PERIOD><![CDATA[1]]></E.PAY_PERIOD>
</row>

In my XML file there are 1333 rows. Rows numbered 1 to 535 have E.PAY_PERIOD equal to 3, and rows numbered 536 to 1333 have E.PAY_PERIOD equal to 1. When E.PAY_PERIOD is equal to 3, B.PAY_BEGIN_DT is equal to 2017-08-16 for rows 1 to 535, and when E.PAY_PERIOD is equal to 1, B.PAY_BEGIN_DT is equal to 2017-08-30 for rows 536 to 1333.

What I am trying to do is find the minimun E.PAY_PERIOD and when found show the associated B.PAY_BEGIN_DT. This date is needed on a report and I only need to show it one time.

This is what I have been work with:

<?xdoxslt:set_variable($_XDOCTX, ‘minPayPeriod’, xdoxslt:minimum(E.PAY_PERIOD))?>

<?choose:?>
<?when:xdoxslt:get_variable($_XDOCTX, ‘minPayPeriod’)?>
      minPayPeriod: <?xdoxslt:get_variable($_XDOCTX, ‘minPayPeriod’)?>
      E.PAY_BEGIN_DT: <?E.PAY_BEGIN_DT?>
      E.PAY_END_DT: <?E.PAY_END_DT?>
<?end when?>
<?end choose?>

The output of this is:

minPayPeriod: 1 <-- this is correct
E.PAY_BEGIN_DT: 2017-08-16 <-- I need this date to be 2017-08-30 since this is the date when E.PAY_PERIOD is 1.
E.PAY_END_DT: 2017-08-29 <-- I need this date to be 2017-09-12 since this is the date when E.PAY_PERIOD is 1.

Also, how can I tell which row number E.PAY_BEGIN_DT and E.PAY_END_DT are from.

I hope I've explained this properly.

Thanks for any help.

Tony

Solution

  • Once you get the minimum value, you will have to search the XML for the node containing that value, and print its corresponding date. Here is some pseducode, I (haven't tested it).

    <?choose:?>
<?when:xdoxslt:get_variable($_XDOCTX, ‘minPayPeriod’)?>
      minPayPeriod: <?xdoxslt:get_variable($_XDOCTX, ‘minPayPeriod’)?>
      E.PAY_BEGIN_DT: <?row [E.PAY_PERIOD = xdoxslt:get_variable($_XDOCTX, ‘minPayPeriod’)]/[0]/E.PAY_BEGIN_DT?>
      E.PAY_END_DT:  <?row [E.PAY_PERIOD = xdoxslt:get_variable($_XDOCTX, ‘minPayPeriod’)]/[0]/E.PAY_END_DT?>
<?end when?>
<?end choose?>

    The /[0] part is to search for the first occurrence, instead of printing all matches.