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.
1422632 | 0 |
1422632 | 1 |
1414505 | 1 |
1420044 | 0 |
1420044 | 0 |
1420044 | 1 |
1413297 | 0 |
1413297 | 0 |
1413297 | 1 |
1417531 | 1 |
Solved! Go to Solution.
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
Thank you RodL, I need to definitely learn some more about how each tool functions.
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!
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.
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?
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?
already solved- thanks.
can you post the solution on the thread please.