Match data in both files based on amount

Bhavishek Malik

We have two file and Amount is only criteria to generate matched report and exception report. Also in both files we have more than 1 occurance of same amount, how can we generate matched report and exception report using Monarch tool.

Baba_Majekodunmi_703

Hi Bhavishek,

You can perform a join in the Data Prep Studio based on Amount. However it will be challenging if that is the only key you can join upon. Do you have a sample of the data you can share, or post a screenshot here?

Here are some links in the community with short videos on how to do a join and edit a join;

• Monarch DPS Essentials Training 7 of 10: LookUp and Join
• Monarch DPS Essentials Training 8 of 10: Edit Joins

 

You can find more videos in the Monarch Success Plan

Bhavishek Malik

Thanks Baba .

 

Here is the sample attached data.

Even though date is in the columns but very rare that date matches in both file.

Also in some case, we have more than 1 occurrences of the same amount.

Can you please suggest how to generate matched report and exception report using Monarch?

 

Thanks,

Bhavishek Malik

Baba_Majekodunmi_703

Bhavishek Malik said:

Thanks Baba .

 

Here is the sample attached data.

Even though date is in the columns but very rare that date matches in both file.

Also in some case, we have more than 1 occurrences of the same amount.

Can you please suggest how to generate matched report and exception report using Monarch?

 

Thanks,

Bhavishek Malik

Hi Bhavishek,

It looks like you're try to do some kind of reconciliation in the accounting and finance realm, based on journal entry dates for some transactions. There must be more information to help you increase the accuracy of the join.

What is the data source of Data 1 and Data 2? What systems are they coming from? Perhaps we have some insight that we can offer. Not only do we know Monarch, we have a lot of systems knowledge too.

That said, attached is the workspace I have designed that has an inner join, as well as a negative inner join, using only amount as the key.

Inner Join

Negative Inner Join

Bhavishek Malik

Hi Baba,

 

Thanks for the response. I see a disconnect here.

In a file, there are 3 entries of -$229 (credit) , but in other file, only 1 entry of -$229 .

As per your matched lookup, it is showing all 3 entries of -$229 matched whereas it is not true.

Only 1 entry matched and rest will go to exception report.

 

As far as system is concern, we are using Fiserv system for getting 1 file but we are capturing data from other tool which is not part of Fiserv ( we got an investment in another bank and we are pulling the report where only amount matched even posting date will not matched along with description).

 

Awaiting for your response regarding how to match where same amounts occurs multiple times.

 

 

Thanks,

Bhavishek

Baba_Majekodunmi_703

Bhavishek Malik said:

Hi Baba,

 

Thanks for the response. I see a disconnect here.

In a file, there are 3 entries of -$229 (credit) , but in other file, only 1 entry of -$229 .

As per your matched lookup, it is showing all 3 entries of -$229 matched whereas it is not true.

Only 1 entry matched and rest will go to exception report.

 

As far as system is concern, we are using Fiserv system for getting 1 file but we are capturing data from other tool which is not part of Fiserv ( we got an investment in another bank and we are pulling the report where only amount matched even posting date will not matched along with description).

 

Awaiting for your response regarding how to match where same amounts occurs multiple times.

 

 

Thanks,

Bhavishek

Hi Bhavishek,

 

I spoke with my colleague internally about this, I will reach out to you shortly about this.

Bhavishek Malik

Baba Majekodunmi_21731 said:

Hi Bhavishek,

 

I spoke with my colleague internally about this, I will reach out to you shortly about this.

Hi Baba,

Thanks for the update.