Data Compare with Numerical Tolerance Help

[zwanphony]

Can someone please show me the accepted inputs/formatting for the numerical tolerance value field in BC?

Also, is it required that a "key" be set? I don't have any keys other than three sets of numerical values that have to be within 250/1000 of each other.

Thanks in advance and my search didn't provide any answers so sorry in advance if this has already been asked.

Love BC!

[Aaron]

Hello,

For the Data Compare, you will need a Key defined in order for two rows to align. The key must be an exact match and unique. A common example is an "ID Number" column. You can also set multiple keys, which together form the key: like a "First Name" and "Last Name" column pair, both set as Key.

By default, the key is also sorted, which helps them align.

Once the rows are aligned, you can then set a numeric tolerance on another, standard column. This is done in the Session menu -> Session Settings, Columns tab, select the numeric column, and you can then set the tolerance here (once you uncheck Use Default, and assuming it is numeric).

If you are still having any trouble, would it be possible to email in sample files and a screenshot? You can email us at [email protected] along with a link back to this forum thread for our reference.

[zwanphony]

Hi Aaron,
Thanks for your prompt response. I was hoping that I could specify a tolerance on (3) coordinate columns and if all (3) cells in a particular row are in tolerance, that would act as a key. I don't have any spefic key other than measurements from a laser shoot. I was trying to align the coordinates based on tolerance and have it done quickly via BC rather than writing my own VBA script.

a sample would be as follows:

Input File 1
X	Y	Z	Msr 1	Msr 2	Msr 3	Pass/Fail
1.25	-1.3	5.7	-0.001	-0.023	.032	FAIL
1.01	-2.4	3.3	-0.001	-0.003	.009	PASS
1.01	-2.1	3.6	-0.002	-0.015	.024	FAIL
1.25	-1.7	5.3	-0.001	-0.006	.007	PASS

Input File 2
X	Y	Z	Msr 1	Msr 2	Msr 3	Pass/Fail
1.25	-1.8	5.3	-0.002	-0.009	.007	PASS
1.25	-1.2	5.6	-0.002	-0.034	.025	FAIL
1.01	-2.1	3.5	-0.001	-0.013	.022	FAIL
1.01	-2.4	3.2	-0.003	-0.006	.013	PASS

BeyondCompare Output/Comparison
File 1								File 2
X	Y	Z	Msr 1	Msr 2	Msr 3	Pass/Fail	X	Y	Z	Msr 1	Msr 2	Msr 3	Pass/Fail
1.25	-1.3	5.7	-0.001	-0.023	.032	FAIL		1.25	-1.2	5.6	-0.002	-0.034	.025	FAIL
1.01	-2.4	3.3	-0.001	-0.003	.009	PASS		1.01	-2.4	3.2	-0.003	-0.006	.013	PASS
1.01	-2.1	3.6	-0.002	-0.015	.024	FAIL		1.01	-2.1	3.5	-0.001	-0.013	.022	FAIL
1.25	-1.7	5.3	-0.001	-0.006	.007	PASS		1.25	-1.8	5.3	-0.002	-0.009	.007	PASS

[Aaron]

Given the current files, we wouldn't be able to support aligning on X,Y,Z. You would need to introduce a Key column (like an identifier ID, or Row number). Given your data, would you want Row 2 of the Left file sorting and aligning with Row 3 of the right file if they happened to be a 'closer fit' within the set tolerance?

If you introduce a new column with Row Number into your files (default could be the first column, but doesn't need to be), you could then set that as the Key, which would align Rows by that number.

Once the Data Compare is running and aligning rows together, you could then set the columns with X,Y, and Z as having a Numeric Tolerance in the Session Settings. But getting the lines to line up is the first step.

[zwanphony]

Thanks again Aaron for your prompt reply. Yes, the Beyond Compare Results section indicates how BC would align the data assuming it could align rows of data based on a numerical tolerance set on (3) keys, one key for each coordinate. I can't just add arbitrary row numbers or IDs as the data is always slightly different. I've tried creatd my own serials by concatenating each coordinate's integer values (left of decimal) to create a serial, but this method fails when a value of 1.9" in a left file is now a 2.1" in a right file (but is within tolerance of 0.25"), so that method doesn't work. Thus why I am seeking a tolerance on a key so the data will align itself properly. I could care less about the tolerance, I just need to get the data aligned and then compare some other specifics.

Thanks for your time. Have a great day.

[Aaron]

Hello,

An interesting scenario. I don't think we have quite the toolset available to organize this data. Sorting and aligning on 'similar' data is something on our wishlist, but not something the Data Compare currently supports.

Instead of concatenating, what if you add all the values together, then sort on that value in Excel before adding an ID? This also would have a downside of reversed values being treated the same, so you would need a secondary sort on X, Y, Z, or something along those lines.