Community Spring Cleaning week is here! Join your fellow Maveryx in digging through your old posts and marking comments on them as solved. Learn more here!

Alteryx Designer Desktop Discussions

Find answers, ask questions, and share expertise about Alteryx Designer Desktop and Intelligence Suite.
SOLVED

Comparing data in each row of a single column

Faithz
6 - Meteoroid

Good Day All,

 

I am new to Alteryx and trying to figure out how to compare consecutive rows from a individual column and then give 0 if it matches and 1 if no match.

 

example:

 

reference number

1422632 
1422632 
1414505 
1420044 
1420044 
1420044 
1413297 
1413297 
1413297 
1417531 

 

then get a output that looks like below.

 

14226320
14226321
14145051
14200440
14200440
14200441
14132970
14132970
14132971
14175311

 

10 REPLIES 10
RodL
Alteryx Alumni (Retired)

You can use the Multi-Row Formula tool. It allows you to assign values to the current row relative to row values above or below the current row.

 

Since you are assigning the flag based on a "future" row, the expression would be something like...

IF Row+1:RefNum != RefNum THEN 1 ELSE 0 ENDIF

Faithz
6 - Meteoroid

Thank you RodL, I need to definitely learn some more about how each tool functions.

RodL
Alteryx Alumni (Retired)

That will come with time. :-)

When I first started using Alteryx and understanding the power behind it, I would say to myself, "I know I can probably do this with Alteryx...just not sure which tool to use."

Didn't have this great community to search for answers to questions like we have now, so just keep on asking the questions!

sekarsdream
8 - Asteroid

I have a similar problem and i used this same formula but my column values turned blank or zero.. is there any way that i can correct this issue?

Faithz
6 - Meteoroid

Hi Sekar,

 

I used this formula below:

 

if [Row+1: ID number]=[ID number] then 0 else 1 endif

 

all I did was change around the 0 and the 1 from the initial formula given to me. 

 

Also the column you are working on must be ordered so similar ID numbers are grouped.

bb213
8 - Asteroid

I have a similar issue but I need to know what row values are contained in other row values anywhere in the dataset.  

 

For example, 

 

1 CS-123     

2 CS-1234      1

3 CS-12345    1

4 DD-1244      0

 

I would want the 2nd and 3rd rows to have a value of 1 because they both contain the value in Row 1. Any ideas? 

Faithz
6 - Meteoroid

Hi bb213,

 

give me a better idea of what your trying to achieve.

 

1 - is it that your row 1 will be base item your searching for? meaning you have a list a x amount of rows, and you want to know from this list, how many match row 1 only?

bb213
8 - Asteroid

already solved- thanks. 

Faithz
6 - Meteoroid

can you post the solution on the thread please.

Labels