Hi,

At the moment the record filter is designed to filter based on static values only. You can add multiple filter criteria, so you can include or reject rows based on the values in more than one column. You can also reject records using the 'Merge/Join' operation by choosing to retain non matching records from either input dataset.

Would it be possible to post some example data and give me a bit more detail about what type of filter operations you need to perform?

Thanks

Chris

Hi Chris,

The fields I am trying to compare are things like quantities, prices, and dates so hard coding values really isn't going to work. I tried like at the merge/join, but he issue is I am comparing too many fields to make that work easily. Basically what I am trying to do is reconcile positions between a thirdparty and the trade repository. Presently I take two files from two different sources that in theory should be identical, but never are exactly. I first merge/join the two files on a common field and would like to use the Data Manager to compare several fields to look for breaks. My idea was to create a "filter" that would reject all records where all fields matched (basically I am not interested in matching records) and let mismatch records fall through so someone can determine why the are different. The record filter would work perfectly for this if I could compare fields instead of just a field and a static value.

Does that help?

Rich

Hi Rich,

From the sound of things you may be able to achieve this using the 'De-duplicate' operation.

De-duplicate, as its name implies, will remove all duplicate records based on the fields provided. So if your dataset has 10 fields and you choose to de-duplicate by 8 of those fields it will remove all records that are identical in those selected 8 fields.

If this does not resolve your problem could you generate a short example and post it as an attachment on this forum.

Chris

I will give that a try and let you know what happens.

On second thought, this will not work because dedup requires separate records does it not? I need to be able to filter on two columns in the same row. Or am I missing something with how dedup works?

Here is an example with two files. I want to compare the price, date, and quantity fields and show all records where they are different. I am merging on the id field.

Hi Rich,

I think you can do this using a combination of two Merge/Join operations:

The first Merge/Join should take the two files as inputs datasets and join on Id1 matching ID2 returning merged records.

In the second Merge/Join you need to take the result of the first join and the second file as inputs and create a join criteria that matches on the price, date and quantity fields (Price1=Price2, Date1=Date2, Quantity1=Quantity2). You should choose to retain 'Non matching records' from the first join only.

The resultant dataset should contain 3 records (ID2,ID4 and ID5). These records all have a different price date or quantity.

I've attached an IOK file with the configuration described above.

If you have any problems opening the attached IOK file here is a screenshot of the DataManager configuration.

Regards

Chris

I was able to get the iok file, I am just trying to apply it to what I was doing. The real files are much larger and I am matching on more columns. I will keep you posted.

Thanks for the help,

Rich