How to get only the earliest record. HCI mapping logic

[Wes_Ancog]

HI i have an xml like this:

<?xml version="1.0" encoding="UTF-8" standalone="no"?>
<Root>
<Emp>
<PersonId>10001</PersonId>
<Child>
<F1>JOE DOE</F1>
<F2>SGP</F2>
<F3>2010-01-06</F3>
<F4>10001</F4>
</Child>
<Child>
<F1>Chris Tiu</F1>
<F2>SGP</F2>
<F3>2012-01-26</F3>
<F4>10001</F4>
</Child>
</Emp>
<Emp>
<PersonId>10653</PersonId>
<Child>
<F1>Test Child</F1>
<F2>SGP</F2>
<F3>2008-11-29</F3>
<F4>10653</F4>
</Child>
<Child>
<F1>jane doe</F1>
<F2>SGP</F2>
<F3>1994-01-05</F3>
<F4>10653</F4>
</Child>
</Emp>
</Root>

for a given userid, say for example userid 10001. i want to get only the record with the latest date, which is "2012-01-26". What is the approach for this in HCI?

[former_member602661 · Accepted solution]

Heya!

I have written a sample code in groovy for you 🙂 you can tweak the code as per the need 🙂

import groovy.xml.MarkupBuilder
class sortXML {
static main(args) {

String body = "<?xml version=\"1.0\" encoding=\"UTF-8\" standalone=\"no\"?><Root><Emp><PersonId>10001</PersonId><Child><F1>JOE DOE</F1><F2>SGP</F2><F3>2010-01-06</F3><F4>10001</F4></Child><Child><F1>Chris Tiu</F1><F2>SGP</F2><F3>2012-01-26</F3><F4>10001</F4></Child></Emp><Emp><PersonId>10653</PersonId><Child><F1>Test Child</F1><F2>SGP</F2><F3>2008-11-29</F3><F4>10653</F4></Child><Child><F1>jane doe</F1><F2>SGP</F2><F3>1994-01-05</F3><F4>10653</F4></Child></Emp></Root>";
        def parseXML = new XmlParser().parseText(body);
def varStringWriter = new StringWriter();
def varXMLBuilder   = new MarkupBuilder(varStringWriter);

int empNodes = 0 ;
int empChildNodes = 0 ;
int childPicker = 0 ;

String empNumber ;
String empF1 ;
String empF2;
String empF3;
String empF4;

String nodeBody ;

empNodes = parseXML.Emp.size();
for(int empItr = 0 ; empItr < empNodes ; empItr++)
{
childPicker = 0 ;
empNumber     = "${parseXML.Emp[empItr].PersonId.text().toString()}"
empChildNodes = parseXML.Emp[empItr].Child.size();
for(int childItr = 0 ; childItr < empChildNodes - 1 ; childItr ++)
{
String empF3Temp ;
empF3     = "${parseXML.Emp[empItr].Child[childItr].F3.text().toString()}"
empF3Temp = "${parseXML.Emp[empItr].Child[childItr+1].F3.text().toString()}"

if(empF3 < empF3Temp)
{
childPicker = childItr+1;
}
}

empF1 = "${parseXML.Emp[empItr].Child[childPicker].F1.text().toString()}"
empF2 = "${parseXML.Emp[empItr].Child[childPicker].F2.text().toString()}"
empF3 = "${parseXML.Emp[empItr].Child[childPicker].F3.text().toString()}"
empF4 = "${parseXML.Emp[empItr].Child[childPicker].F4.text().toString()}"

varXMLBuilder.Child{
F1(empF1);
F2(empF2);
F3(empF3);
F4(empF4);
}

def xml = varStringWriter.toString();
nodeBody= '<Emp><PersonId>'+empNumber+"</PersonId>"+xml+'</Emp>' ;
}

nodeBody='<Root>'+nodeBody+'</Root>';
println nodeBody ;

}
}

Regards

Akash

[Wes_Ancog · Accepted solution]

Thank you!

[apu_das2]

Did you try with mapping mapping.

I guess it can be handled using sort and copy value. Means first sort in descending order so thatlatest date will be the first one in the context and then use copyValu(0) to pick first one.

[Wes_Ancog]

i already did the sorting but i dont know how to do the removal

<?xml version="1.0" encoding="UTF-8"?>

<xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform">

  <xsl:output encoding="cp1252" />

  <xsl:template match="/Root/Emp">

  <xsl:copy>

  <xsl:apply-templates select="Child">

  <!--  concat year, month, day -->

  <xsl:sort order="descending" select="concat(

  substring(F3, 1, 4),

  substring(F3, 6, 2),

  substring(F3, 9, 2)

  )"/>

  </xsl:apply-templates>

  </xsl:copy>

  </xsl:template>

  <xsl:template match="@* | node()">

  <xsl:copy>

  <xsl:apply-templates select="@* | node()"/>

  </xsl:copy>

  </xsl:template>

</xsl:stylesheet>