Formula between cells on different rows of two different columns

[Sander Homan]

Hi. I have a problem, which I could solve in Excel within 5 seconds but I don't know how to solve it in Spotfire.

I have multiple columns with thousands of rows.

I need a formula which does the following:

Check if row 1 of column A is the same as row 2 of column B, check if row 2 of column A is the same as row 3 of column B, check if row 3 of column A is the same as row 4 of column B, check if row 4of column A is the same as row 5 of column B, etc etc. For each record it should give the value 1 (Yes) or 0 (No).

I hope above makes sence.

[Khushboo Rabadia]

You can use over statement in calculated column for achieving your requirement:

- Create a calculated column with RowId() expression which will assign rowids to each row

- Then create a calculated column using over statement:

case when [ColumnA]=First([ColumnB]) OVER (Next([Rowid])) then 1 else 0 end

[Sander Homan]

Thanks, this worked!

I thought this wouldn't workbecause the rowid's are random and not sorted.

 

What's the opposite Expression of Next

In case I want to look to the cell above each row instead of below each row.

Never mind, found it: Previous!