Data Grouping Based on Logic Constraints

[Phillip Wantuck]

I have a table that has the following.

The Alarm_PK column is essentially a "RowID" column. Using the RowID function does not give good results, everything is out of order when compared to the Alarm_Date column.

Alarm_Date - Date timestamp when the alarm occurred.

Group - Desired output. I want to have a calculated column that looks at Alarm_PKandAlarm_Date. If the Alarm_Date for the current row is 15 minutes < the next row's Alarm_Date then assign the following: If it is the first row in a 15 min window assign it Primary. If it is anything other than the first row in the 15 min window assign it Secondary.

Please let me know if I need to clarify anything. I have tried using bin, next/previous, case statements and nothing is producing what I need.

 

 

 

ALARM_PK

ALARM_DATE

Group

 

 

14945

8/19/2014 20:39

Primary

 

 

14946

8/19/2014 20:39

Secondary

 

 

15096

8/27/2014 15:54

Primary

 

 

15097

8/27/2014 16:02

Secondary

 

 

15101

8/27/2014 16:39

Primary

 

 

15103

8/27/2014 17:18

Primary

 

 

15105

8/27/2014 18:00

Primary

 

 

15109

8/27/2014 18:17

Primary

 

 

15111

8/27/2014 18:31

Secondary

 

 

15112

8/27/2014 18:55

Primary

[David Boot-Olazabal]

Hi Phillip,

May this post can help you achieve the outcome you are looking for:

https://community.spotfire.com/questions/previous-row-value-data-table

Kind regards,

David