Multi-row formula to identify changing values

[anujarora1]

Hi Experts,

I am new to Alteryx and facing a difficulty with the below.

I have summarized my issue with a hypothetical example. In the below table, I have a color 'Red' which has two ID's (ID 1, 2) having set of values (same or changing). In my original problem, I have many different colors and my goal is to identify/flag all those colors which have changing values, like in the example below. Even if one value is different than the rest, I want to highlight each row of color 'Red'.

Thanks in advance!

Color	ID	Value
Red	1	100
Red	1	100
Red	1	100
Red	1	100
Red	1	100
Red	2	100
Red	2	80
Red	2	60
Red	2	40
Red	2	20

[atcodedog05 · Accepted solution]

Hi @anujarora1 

Here is how you can do it.

Workflow: Checking on Color and ID

1. Using summarize tool groupby color and ID to distinct values count.

2. Using formula if distinct value count >1 then flag changing value as 1.

3. Using join to join back the flag to the main on color and id.

4. Using table applying column rule to highlight values of changing value.

Or checking only on color is values changing

Hope this helps : )

[Luke_C · Accepted solution]

Hi @anujarora1 

You don't even need to use a multi-row formula, you can just summarize and count the distinct values for each color/ID. If there's more than one distinct value I wrote a formula to flag it.

Edit: missed the nice formatting part of it, but @atcodedog05 has you covered

[anujarora1]

@Luke_C @atcodedog05 Many thanks for the solution! It worked 🙂

I have another case where instead of a number, I am looking for a string. So in the below table, I have unique Deal no. with different types. I want to build a formula that for a same deal if at least one row has Averaged type, I want to write 'Averaged' in all rows, similar to the Outcome column.

Thanks a lot in advance!

Deal	Type	Outcome
18208145	Fee	Averaged
18208145	Fee	Averaged
18208145	Fee	Averaged
18208145	Fee	Averaged
18208145	Fee	Averaged
18208145	Fee	Averaged
18208145	Averaged	Averaged
18208145	Averaged	Averaged
18208145	Averaged	Averaged
18208145	Averaged	Averaged
18208145	Averaged	Averaged
18208145	Averaged	Averaged
18208145	Averaged	Averaged
18208145	Averaged	Averaged
18208145	Averaged	Averaged

[atcodedog05]

Hi @anujarora1 

If the above response helped can you mark them as solution. Let me look into the new usecase.

[atcodedog05]

Hi @anujarora1 

Here is how you can do it.

Workflow:

1. Using filter tool to take only average values.

2. Using groupby on Deal and Type. Only Deals which has Average will be present in this data

3. Using Join multiple to do an outer join.

4. Using formula tool if outcome was not mapped (type average is not there) then take value from type.

Hope this helps : )