Only one column selection in a day

Question

There are some data that uploaded from equipment sensor automatically.

(I attached the part of sample data)

Time[column 2] is saved like ''2017-10-14 02:01:26:396'.
Everyday, several thousands of data is saved automatically in a day.

Is there good method to select only one row in a day ?

I want to know three kinds of selection method.

case 1 : select the first row of the day

case 2 : select the last row of the day

case 3 : select random one row of the day

only one row selection in a day.xlsx

brady_brady · Accepted Answer

Hi Ikju,

Assuming you are willing to create new columns, here is a simple way. There are other ways with more scripting, but this is most straightforward:

1) Create a new formula column to just capture the day, using a column transform: Right-click on the column header of the datetime column and select New Formula Column > Date Time > Date. Name this column "Date2", for example.

2) Create a column cumulative sum formula column: create a new column and give it this formula: Col Cumulative Sum (1, :Date2)

3a) To select the first row of the day, sort your original datetime column, ascending. Then select all cells in the cumulative sum column that equal 1.

3b) To select the last row of the day, sort the original date time column, descending. Then select all cells in the cumulative sum column that equal 1.

3c) To select a random row from each day, create a new random uniform (0,1) column. Sort by this column, and select all cells in the cumulative sum column that equal 1.

There are other methods that use JSL; if that's what's needed please let me know.

Cheers,

Brady