Sending two files with duplicate records at the same time

[Former Member]

Hi Experts,

I want to joined two data files together, but they have different data fields, the same records such as employee Lucy Smith (ID: 123) as below appear in both files and the duplicates have to be removed. I am wondering how is it possible to be done in SAP PI, using ccBPM?

Below is the sample data:

1. Data in file 1, the first data field is Employee ID:

    234, Debbie, White

    123, Lucy, Smith

    225, Mark, Jones

2. Data in file 2, the first data field is Employee ID:

    567, Lisa, Brown, Research and Development, Contractor

    896, Kevin, Davis, Sales, Regular Employee

    123, Lucy, Smith, Human Resources, Regular Employee

The expected output data file should look like:

    234, Debbie, White, ,

    123, Lucy, Smith, Human Resources, Regular Employee

    225, Mark, Jones, ,

    567, Lisa, Brown, Research and Development, Contractor

    896, Kevin, Davis, Sales, Regular Employee

Thanks in advance!

Blue

[justin_santhanam · Accepted solution]

Hi Blue,

How do you know which one is correct? In your example 123 is duplicated, but you have taken the data from File 2 for the final output. How do you make that call in run time?

Can you provide the data in XML?

Will it be like this for an example?

<Record>

<Data>234, Debbie, White</Data>

<Data>123, Lucy, Smith</Data>

<Data>225, Mark, Jones</Data>

</Record>

Or

<Record>

<row>

<ID>234</ID>

<Data>, Debbie, White</Data>

</row>

<row>

<ID>123</ID>

<Data>, Lucy, Smith</Data>

</row>

<row>

<ID>225</ID>

<Data>, Mark, Jones</Data>

</row>

</Record>

I think I have explained you in the other thread collecting the files in not a problem. Again I'm saying collecting the file is not a problem in BPM or ccBPM, it's pretty simple task. You main concentration/question should be how you are going to merge it. That's the question you should really ask..

Thank you,

Justin.

[Former Member · Accepted solution]

Thank you Justin.

This is still related to the last thread, just because the other one is getting too long and I think I didn't make the question clear on the other one, that's why I created this thread.

That is my worry too now, how to merge the records from different files. I see an example from this blog, but it seems collecting different data fields from different files, and it is different from my case.

[former_member186851 · Accepted solution]

Blue try as per the logic I suggested.Might work.

[Former Member · Accepted solution]

Hi Raghuraman,

How do you think I should do the mapping for my case? it should be different from the one below, since I want to map Employee ID from both files, the one below just getting Employee ID from one file:

[former_member186851 · Accepted solution]

Blue,

In all the three records same EMP records will be sent?

In that case your mapping is correct(your correlation ID will be EMPID).

[Former Member · Accepted solution]

Hi Raghuraman,

Thank you very much, and I will try it.

Blue

[justin_santhanam]

Hi Blue,

We will take a step back. Can you provide your two files structure, here please? We can define dummy correlation in case if you want to collect all the files without any conditions.

- Give us the structure for two files (with sample data)

- Explain what you mean by duplicates, how you wanted to see the final result

We will explain later how you can merge it and how to remove duplicates.

Thank you,

Justin.    

[Former Member]

Hi Justin,

Here are the sample data files, they are all CSV files, so you can see employee ID 123 appears in both files, employee 123 will be a duplicate:

1. File 1, the first data field is Employee ID:

    234, Debbie, White

    123, Lucy, Smith

    225, Mark, Jones

2. File 2, the first data field is Employee ID:

    567, Lisa, Brown, Research and Development, Contractor

    896, Kevin, Davis, Sales, Regular Employee

    123, Lucy, Smith, Human Resources, Regular Employee

The expected output data file should look like:

    234, Debbie, White, ,

    123, Lucy, Smith, Human Resources, Regular Employee

    225, Mark, Jones, ,

    567, Lisa, Brown, Research and Development, Contractor

    896, Kevin, Davis, Sales, Regular Employee

[justin_santhanam]

Blue,

I'm sorry if I wasn't clear.. Please provide the XML structure.. I already asked you this earlier .. We are all trying to help each other here, I hope you understand that. As much as I wanted to provide solid solution, I need help from you as well. I need the XML structure so that I can give you proper answer. If you think that's feasible then provide it, if not I completely understand!

Thank you,

Justin.

[Former Member]

Hi Justin,

I am sorry for didn't understanding what you are asking. It is totally ok to provide the XML data structure. The problem is I didn't create the PI objects yet, so at this moment I don't have the XML data structure. I want to find out if it is possible that ccBPM can handle the scenario I described first or not.

I will create a test job with the sample data later and provide the XML data structure.

Blue

[justin_santhanam]

No worries Blue. Thanks for understanding me, at this point of time we can link references or some hypothetical answers which might not work when you actually implement it. That's why we need proper information before starting .. So I don't want to guide you in the wrong way without knowing what exact the requirements are. Give the XML structure then we can see how to optimize the solution. Please wait for other experts input on your question.

Have a great day!

Thank you,

Justin.

[former_member186851]

Hello Blue,

Merge the files using CCBPM.

http://scn.sap.com/community/bpm/blog/2014/01/30/simple-bpm-scenario

Then at the mapping level remove the duplicate records.

http://scn.sap.com/community/pi-and-soa-middleware/blog/2013/12/23/remove-duplicate-records-in-messa...

[Former Member]

Thank you Raghuraman.

I have saw the both links, but the first one seems not my case, since mine needs to collect different RECORDS from two different files. The ccBPM in the link seems collecting different DATA FIELDS from the different files.

I wonder how can I merge the records from different files.