Forum Discussion
create new column based on two cells from different with same value
Hey there,
I have following situation which I couldn't resolve yet:
I want to compare each row with the previous row.
If Colour and Previous Colour are both the same as in the row before I want to skip to the next row automatically and try it again.
If they are not the same, in an additional column it should add the value "1"
| ID | Colour | Previous Colour | Index |
| 126443 | Red | Green | 1 |
| 123456 | Blue | Red | 2 |
| 175332 | Blue | Red | 3 |
| 765432 | Yellow | Blue | 4 |
| 733511 | Green | Yellow | 5 |
| 531534 | Blue | Green | 6 |
Something like:
if ((PrevColour(index-1) = PrevColour(index) && Colour(index-1) = Colour(index))
then jump to index+1
else type "1" in new column
I'd appreciate any help. Kind regards 🙂
Jama1234
1. Add a new column: Index2 as an index column starting at 2 (one more than column Index)
2. Do a self join (Merge Tables) with Index and Index2 as the key joining columns. Kind of join is left outer
3. Expand the newly generated column to new rows.
4. Now you have duplicate columns in your table with values equal to the previous row.
5. Add a column that compares the current values with the previous values that are stored in the same row.
6. Remove unneeded columns
2 Replies
1. Add a new column: Index2 as an index column starting at 2 (one more than column Index)
2. Do a self join (Merge Tables) with Index and Index2 as the key joining columns. Kind of join is left outer
3. Expand the newly generated column to new rows.
4. Now you have duplicate columns in your table with values equal to the previous row.
5. Add a column that compares the current values with the previous values that are stored in the same row.
6. Remove unneeded columns