Data Validation

[vijaylnyadav]

Hi,

In a scenario, I want to compare a data from a two specific column correlated in one excel file with two columns in another excel files.

I have done a join multiple and got the output as a two columns from first excel files and two columns from another excel file in a single file. Now I want to compare. Please suggest a tool and solution for the same.

[PhilipMannering]

If your columns are now lined up in a single table (by virtue of the Join Multiple tool). You can now use the Filter Tool or Formula Tool to compare them. Something like,

if [col1] = [col2] then 'Match' else 'Not Match' endif

in a formula tool.

Or simply,

[Col1] = [Col2] 

 in a filter tool. It depends what you want to do!

[vijaylnyadav]

Column1	Column2	Column 3	Column 4
XYZ	1	XYZ1234	01
ABC	2	ABC4322	04
MNO	3	MNO2341	06

Consider a sample data,

Here, i want to compare column3 & column4 with Column1 & column2

For Eg,

number in column 2 should match with number with column 4 condition is that string in column1 should match with first 3 letter in Column3.

If ABC = 2 then ABC4322 should be equal to 2 (not 4) considering the relation with ABC = 2.

Please suggest some solutions

[AngelosPachis]

Hi @vijaylnyadav ,

I agree with the solution provided by @PhilipMannering previous post. What you have to do is use a filter tool, to compare Column2 with Column4. It would look something like this:

You will probably also have to use a select tool to change the data type of column 4 from string to a numeric type (byte, int or double) prior to your filter tool

[vijaylnyadav]

Please refer the given sample data sets and provide me a solution

[atcodedog05 · Accepted solution]

Hi @vijaylnyadav 

Here is a workflow for the task.

Output:

Workflow:

Hope this helps 🙂 Feel to ask if you have any questions

If this post helps you please mark it as solution. And give a like if you dont mind 😀👍

[PhilipMannering]

As you command. See attached.

[vijaylnyadav]

Thanks for your solution,

This is what I was looking for.

[atcodedog05]

Happy to help 🙂 @vijaylnyadav 

Cheers and Happy Analyzing 😀

Feel free to reach out if you face any issues 🙂

[vijaylnyadav]

Column1	Column2	Column 3	Column 4
XYZ	1	XYZ1234	01
ABC	2	MNO2341	04
MNO	3	ABC2341	06

Consider a sample data,

Here, i want to compare a specific data not the whole column.

For Eg,

If ABC = 2 then ABC4322 should be equal to 2 (not 6) considering the relation with ABC = 2 and if MNO = 3 then MNO2341 should be equal to 3 (not 4) without sorting the columns

Please suggest some solutions