Sander Homan Posted January 4, 2019 Share Posted January 4, 2019 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. Link to comment Share on other sites More sharing options...
Khushboo Rabadia Posted January 4, 2019 Share Posted January 4, 2019 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 Link to comment Share on other sites More sharing options...
Sander Homan Posted January 7, 2019 Author Share Posted January 7, 2019 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! Link to comment Share on other sites More sharing options...
Recommended Posts
Create an account or sign in to comment
You need to be a member in order to leave a comment
Create an account
Sign up for a new account in our community. It's easy!
Register a new accountSign in
Already have an account? Sign in here.
Sign In Now