Compare a few columns and show result where they differ

[AshleyTolliver]

I am trying to compare a few columns and show where two columns are different.  I tried using the Join tool but for some reason it's duplicating instead of comparing

[jrlindem]

I would recommend you use a Formula to directly compare the two columns of data (assuming they're already in the same stream).

Otherwise, if you're interested in third-party Macro's, CReW Test macros are great.  The Expect Equal Tool in particular is useful for this.  -Jay

[AshleyTolliver]

They are in the same stream. I am new to alteryx and I guess I don't know how to write the formula.  The end goal is to say for this business, this customer, this payment type and this product, show me where there is a difference and mark is as an issue.  So i would be focusing on payment type. 

For Example-

Lowe's, John Doe, Credit Card, Paint

Lowe's, Jane Doe, Cash, Paint

Lowe's, Jim Doe, Credit Card, Paint

I would want to flag these an issue.

[jrlindem]

Can you attach a small sample of dummy data. I can build around that to show you.  -Jay

[AshleyTolliver]

I hope this is what you were looking for....

[jrlindem]

@AshleyTolliver your original post stated that you were comparing two columns.  In the data you provided:

All the columns are naturally different from each other because they are inherently different fields.  Can you explain further what you are comparing against?

What I assume you have is two data sources, one for the source data and one that you're comparing against.  I'm calling this "Point of Sale Data" and "Inventory System Data".

In the screen shot (and attached) I just used your data twice, added a Transaction ID field and modified one record to show a difference (for illustration).

I joined on the Transaction ID and then showed two methods.  One that compares each field one at a time, and the other shows how you can concatenate fields and then compare them.

I realize that this may not be exactly what you’re after, but I hope it helps teach, for instance, the concepts of comparing columns of data.

Let me know based on this illustration how this relates t your use case and how you need to start thinking about your data for comparison.  Then I can help home in on your use case if needed.  -Jay

[AshleyTolliver]

So, what I am trying to do is show where the payment type is different within the same Business, same product type. If the payment type is different within the same business for the same product, i want to flag that as an issue.  

[jrlindem]

Okay, for that I would go a different direction.  Stated differently:  For every combination of Business and Product, I want to know how many different Payment Types there are.  If the result is > 1 then I have an issue.

Change approach and use the summarize tool to group by Business and Product.  Then simply Count Distinct Payment Types.  Use a Filter Tool to show where that Distinct Count is > 1

[AshleyTolliver]

How do I now see the records that are issues? I have the filter showing me my "2's" but how do i now see all the records that are making up the "2's"?

[jrlindem · Accepted solution]

With a little more configuration you can't achieve this.  Two outcomes, one where it shows what all the different Payment Types are and the other where it flags the records.

I think with all the examples provided; you should be able to adapt the concepts to achieve what you're after!  I would recommend you start working to learn these tools and how they can be modified to suit your needs.  If you run into any new barriers, open a new thread with that specific question!

Hope that helps, -Jay