Urgent: Remove duplicates

[Former Member]

Dear Experts,

I have an urgent but bit complicated duplicated records case here, how can I remove the duplicates:

Field name for file 1 as:

ID, Name, department, reference number

Field name for file 2 as:

ID, Name, department

I have data records from two data files below, the first record in file 1 and the one record in file 2 are the same, they are duplicated, because the Reference Number for the record in file 1 is the same as the ID number of the record in file 2. I am just wondering how can I using PI mapping to discard the record from the second file

file 1

123, John smith, sales, 567

234, Mike Bank, HR,

file 2

567, John smith, sales,

Thanks a lot for any input!

Blue

[ridvan_polat · Accepted solution]

Hi,

You may achieve it like below,

I assume you got the files in to mapping by configuring two separate message type in Signature tab.

If your source and target structure are like below, then you can try solution 1. If not, try solution 2.

Solution 1

Solution 2

I hope has been useful.

Ridvan Polat

[Former Member · Accepted solution]

Hi Ridvan,

Thank you very much for your reply!

I forgot to mention that file 2 has other records which are not duplicates. if I can create my source and target message as showed in the XML from your post, that would be idle. Seems solution 2 would work, but I don't see how could other non duplicated records from file 2 be sent to target?

ALso is it possible to create a source message showed in your post from two different files?

BLue

[ridvan_polat · Accepted solution]

Hi Blue,

If you have more fields I recommend you the first solution.

You can add multi source message types like below.

If you go on with second solution, then you need to use 'if-withoutelse' condition before mapping every field.

Regards,

Ridvan Polat

[Former Member · Accepted solution]

Thank you Ridvan.

Do you think if solution 1 will achieve what I want as below:

file 1

123, John smith, sales, 567

234, Mike Bank, HR,

file 2

567, John smith, sales,

789, Debbie white, HR,

My result:

123, John smith, sales, 567

234, Mike Bank, HR,

789, Debbie white, HR,

Blue

[ridvan_polat · Accepted solution]

Yes, I think so

[Former Member · Accepted solution]

Great!

Sorry one more question, from the mapping graphical, I don't see the mapping for file2 like

File2/name ----> target/name

File2/ID -------> target/ID

I am wondering if records from file 2 will go to target

Thanks

Blue

[ridvan_polat · Accepted solution]

Hi Blue,

I don't know your message structure.

In my example I named them as 'file1', 'file2', 'target' etc.

They are representing your field names in shown hierarchy.

Ridvan.

[Former Member · Accepted solution]

Hi Ridvan,

I created a test mapping, can you help me take a look if the first step is correct, also how do I map the ID in the red box from the second image to the target EmpID data field?

1. If the first step below is correct

2. Also since I've already mapped the ID from message 1 to target EmpID, how do I map the ID from message 2, the red box from the second image to the target EmpID data field?

Blue

[Former Member · Accepted solution]

Can any experts help me on the mapping above? Is it even possible to be done in PI?

[maheswarareddykonda · Accepted solution]

Hi,

are you using BPM?

i think you should not use such a way until unless go for either BPM or java mapping to merge first and map as per your condition.

so it seems as of now your case is N:1

please note that PI can support only 1:1,1:N until unless go for above statement.

2. Also since I've already mapped the ID from message 1 to target EmpID, how do I map the ID from message 2, the red box from the second image to the target EmpID data field?

target fields  accept only one input not more than one..

in case you have two fields then you thinking to compare or some thing you do that first and then map to target field.

ex: fieldA-------equalS-----target1

      fieldB-------

[Former Member · Accepted solution]

Thank you Maheswarareddy so much!

If N:1 in PI is possible or not is the part I never been sure, thanks for clarifying that.

For our case, the client don't suggest us use BPM.

Can you explain to me in detail

1. How to use JAVA mapping to the N:1 first?

2. Then normal mapping to remove the duplicate records, but to remove the duplicate records, it is NOT comparing the same field, like ID, it is comparing ID to reference number field?

Thanks again!

Blue

[maheswarareddykonda · Accepted solution]

Hi,

you just use the same code had written in above blog. also Note below steps.

1.take the java code from above blog.

2.execute in NetBeans or eclips tool then class file will be generated to use.

3.go to ESR-->create an import archive and import that class file then save and activate.

4.create an operational mapping(don't forget to check the option read attachment) and use java class under type of mapping.(initially you are not going to use any graphical mapping with any condition)

5.execute the interface end to end and then you will see an output of mapping in monitoring tool.

6.depending on that create an structure for source(make sure that output data should match in you new structure).

7.create one  more structure for target ..which you have to share target system.

8. now create an graphical mapping and use in operational mapping after java mapping. by this point you done with ESR.

9.create an source channel as explained above blog.remaing steps all are same.

[Former Member · Accepted solution]

I read "Merge multiple files without BPM", but seems the target structure contains two sub structures which corresponding to each input file. For my case, I need the target structure has only ONE sub structure, both files' structure has to be mapped to that one.

Do you think if this will be possible? If so, how to implement it?

[maheswarareddykonda · Accepted solution]

you no need to worry about that...all you have to do is , just make chnage in below line instead of "Attachment1" try with you second file root node name(under MT node)

Element element = (Element) attachmentDoc.getElementsByTagName("Attachment1").item(0); 

[Former Member · Accepted solution]

Thanks again Maheswarareddy!

Can you take a look at the questions below in red for the step 5 to 7:

5. execute the interface end to end and then you will see an output of mapping in monitoring tool.

    - How to execute the interface end to end? the Interface here referring Service Interface or the whole PI job include both ESR and ID objects

    - Will two Outbound Service Interlace be created? one for each file?

    - Does this mean no need to use input files and any object from Integration Directory, like communication channel, receiver determination, etc

6.depending on that create an structure for source(make sure that output data should match in you new structure).

  - What is the new source structure suppose to be? By this point, should I still create to Outbound Service Interface which can take one input file each

7. create one  more structure for target ..which you have to share target system.

   - Does this mean I can create the target structure required by the client? I don’t understand the sentence “which you have to share target system”

By using this approach will this be able to remove the duplicate correlated by Reference number from file 1 and ID from file 2?

[maheswarareddykonda · Accepted solution]

Hi Blue,

5. execute the interface end to end and then you will see an output of mapping in monitoring tool.
    - How to execute the interface end to end? the Interface here referring Service Interface or the whole PI job include both ESR and ID objects

    - Will two Outbound Service Interlace be created? one for each file?

i assume you are handling xml files not txt files. now you cann create dummy Service interface for source one.

as i was saying make small change in java code and import that in ESR and add in Operational mapping.

also sender channel you have to create as explained in blog(please check screen shot how they configured)

and the start testing(you have to place those two files that you want to process at time in source path.)

and once source channel picked up java mapping will just merge those two files structures in single.

then as per that xml you have to create one more structure to use in second mapping , in this mapping you have to use your actual target structure.

7. create one  more structure for target ..which you have to share target system.
   - Does this mean I can create the target structure required by the client? I don’t understand the sentence “which you have to share target system”

Yes, you have to create structure as per client.

By using this approach will this be able to remove the duplicate correlated by Reference number from file 1 and ID from file 2?

yes , we can do delete duplicate values in second mapping.