Select distinct records in Mapping with no Key field (all fields can vary)

[Former Member]

Hi Experts,

Let me take an example (not the actual requirement but same scenario) to explain the problem where I need your help to get best possible way to resolve. This has to be achieved in mapping, don't have other options as its part of complex end 2 end scenario.

I have following input XML:

<Employee>

<Details>

<Id>123</Id>

<Name>ABC</Name>

<Role>Manager</Role>

<Area>Bangalore</Area>

</Details>

<Details>

<Id>123</Id>

<Name>ABC</Name>

<Role>Manager</Role>

<Area>Pune</Area>

</Details>

<Details>

<Id>123</Id>

<Name>ABC</Name>

<Role>Advisor</Role>

<Area>Bangalore</Area>

</Details>

<Details>

<Id>123</Id>

<Name>ABC</Name>

<Role>Manager</Role>

<Area>Bangalore</Area>

<Details>

<Id>143</Id>

<Name>ABC</Name>

<Role>Manager</Role>

<Area>Bangalore</Area>

</Details>

</Employee>

The output XML is:

<Employee>

<MainRec>

<Id>123</Id>

<Name>ABC</Name>

<table name = 'Roles'>

<record>

<Id>123</Id>

<Role>Manager</Role>

<Area>Bangalore</Area>

</record>

<record>

<Id>123</Id>

<Role>Manager</Role>

<Area>Pune</Area>

</record>

<record>

<Id>123</Id>

<Role>Advisor</Role>

<Area>Bangalore</Area>

</record>

</table>

</MainRec>

<MainRec>

<Id>123</Id>

<Name>ABC</Name>

<table name = 'Roles'>

<record>

<Id>143</Id>

<Role>Manager</Role>

<Area>Bangalore</Area>

</record>

</table>

</MainRec>

</Employee>

As you can see from the example above, here I want to populate only distinct records under table, but there is no key fiield to ditunguish. Any of the 3 fields (Id, Role,Area) can vary and between 2 records if all of these fields are same then its duplicate else select it. So in above XML just discard the 4th record from the source XML and populate all others. Each record has to be checked against all other records all 3 values (ID, Role, Area). Only when none of the records have exactly the same values, populate it.

Also records with different ID come under different table node. Hope my requirement is clear, if not please let me know, i will try to explain better.

I thought of creating a UDF to achieve this but not able to decide how to match it to the output message here.

Best Regards,

Pratik

[arunneerolil · Accepted solution]

You can do this using two advanced UDFs

u1. Extract unique id/name combinations and map these two to the target

u2.To extract the remaining 3 fields and map to the records structure

But you will have play around with the contexts to get these assigned correctly.

arun

[Former Member · Accepted solution]

Hi Arun,

I am mainly struck with the context handling part. Have made a UDF with a global map to have key/value pairs. Each new entry is checked against the previous entries.But as the map has values of all the 3 nodes separate by delimiter, as of now I am unable to map it to the right context with the right number of records.

Any alternative/suggestions would be helpful.

Best Regards,

Pratik

[markangelo_dihiansan · Accepted solution]

Hi,

For the main record, I think you only need to check for each unique ID, e.g

Id --> removeContext --> sort:ascending --> splitByValue:valueChanged --> collapseContext --> MainRec

For the record, however, you need to create a UDF that will filter out the duplicate values. For this, the UDF sample mentioned here contained multipleResult lists

Id --> removeContext --> concat: : --> concat: : --> UDF --> splitByValue:ValueChanged --> record
role --> removeContext --> /          /                \ --> Id
area --> removContext -------------> /                  \ --> role
                                                         \ --> area

Context type UDF

Arguments: input

Result: IdResult

Result: roleResult

Result: areaResult

Vector temp = new Vector();
for(int a=0;a<input.length;a++){
   if(!temp.contains(input[a])
         temp.add(input[a]);
}
for(int a=0;a<temp.size();a++){
   String tmp = (String) temp.get(a);
   /*split according to field */
   IdResult.addValue(tmp.substring(0,tmp.indexOf(":")));
   roleResult.addValue(tmp.substring(tmp.indexOf(":")+1,tmp.lastIndexOf(":")));
   areaResult.addValue(tmp.substring(tmp.lastIndexOf(":")+1,tmp.length()));
}

note: Id and record will both be using the IdResult list.

Hope this helps,

Mark

[Former Member]

Hi All,

Thanks for the inputs. I achieved it using a global Hashmap with a UDF to match the fields.

Best Regards,

Pratik