topic Re: Comparing rows values for multiple values and highlight unmatched col values. in Alteryx Designer Desktop Discussions

Comparing rows values for multiple values and highlight unmatched col values.

Pragya_Shree — Thu, 09 Dec 2021 05:24:14 GMT

Problem: I have a scenario where I have around 50 columns and one ID column( not unique), hence we group by the data on the id column then suppose I have 5 rows for the same id. Now I need to make sure that the rest of all the 50 columns except id col should have similar data across respective columns if in any row a column value is not as per requirement then it needs to be highlighted.

Example:

Id	Name	Country	City	Work
01	John	US	NewYork	IT
01	John	US	Michigan	IT
02	Sam	Australia	Sydney	Radio
02	Love	Australia	Sydney	Radio

Result:

Id	Name	Country	City	Work
01	John	US	NewYork	IT
01	John	US	Michigan	IT
02	Sam	Australia	Sydney	Radio
02	Love	Australia	Sydney	Radio

Note: I know we can use Multi-row but this tool can only be managed for a few columns I need the same thing for more columns at once.

Thanks in Advance.

Re: Comparing rows values for multiple values and highlight unmatched col values.

Jonathan-Sherman — Thu, 09 Dec 2021 07:43:55 GMT

Hi @Pragya_Shree,

An approach with pivoting and determining the mode (most common) value for each group in each column would work here:

I've attached my workflow for you to download if needed!

Kind regards,

Jonathan

Re: Comparing rows values for multiple values and highlight unmatched col values.

Jonathan-Sherman — Thu, 09 Dec 2021 07:49:03 GMT

The difficulty in this case comes where you only have two records (so there is no way to easily distinguish the mode), however I would think in your dataset you have a large number of records so this wouldn't be an issue?

Re: Comparing rows values for multiple values and highlight unmatched col values.

Pragya_Shree — Mon, 17 Jan 2022 06:48:16 GMT

@jonathan Thank you so much! it worked perfectly with a large amount of data.