Easy way to extract only first/last value (based off a time stamp) of a measurement for each serial number?

cweisbart

Customer scenario: 

-3 columns: "serial number", "time stamp", and "measurement value".

-Each serial number will have multiple time stamps associated with it where several measurements were made.

-Customer is only interested in seeing the first and last measurements for each serial number and would rather avoid creating a formula. In some cases each serial number will have the same number of measurements, in other cases it might not.

 

Is there an easy way to do this?

 

Thanks in advance!

brady_brady

Hi, here is one easy way.

1) Right-click on the timestamp column and sort descending/ascending, to keep newest/oldest data, respectively.

2) Select the serial number column, and only the serial number column.

3) Rows > Row Selection > Select Duplicate Rows. Delete these selected rows, if that's OK. If not, proceed to steps 4 and 5.

4) Rows > Row Selection > Invert Row Selection.

5) Tables > Subset and subset the selected rows, keeping all columns.

 

Cheers,

Brady

 

 

Edit: I notice the customer wants first AND last. It's most straightforward for the customer to make 2 subset tables as above, one for first, one for last, and concatenate them. But, here is another way in case it is preferred. I don't know why it would be in this case, though.

 

1) Create a column of ones. I'll call this column ones.

2) Right-click on the timestamp column and sort ascending.

3) Right click on the serial number column, select New Formula Column... > group by.

4) Right-click on the ones column, select New Formula Column > Row > Cumulative Sum

5) Right-click on the new Cumulative Sum column, select New Formula Column > Distributional > Rank (reverse order)

6) Select Rows > Row Selection > Select Where...

7) Using the dialog select where the Cum Sum column == 1 OR where the Reverse Rank column == 1. (Make sure the "any condition met" is selected.)

8) Subset this selection.

 

 

*** I understand the customer does not wish to write a formula... but I would be remiss if I did not point out how simple the formula-based approach is, just for the sake of comparison. Note that this approach also requires no sorting. Here :TS is the timestamp column and :SerNum is the serial number column.

 

1) Create a column with the formula

Col Min( :TS, :SerNum ) < :TS < Col Max( :TS, :SerNum )

2) Select rows == 0, you'll have what you want. Done.