Select - Datetime: Unique

The Unique template appears in template list when a datetime field has been picked in the Select pane.  The template selects records based on how many times their values occur in a table.

 

See the Transform - Datetime: Copy topic for examples illustrating use of the various date and time parts.

 

 

Unique

Selects records based on how many times their values occur in a table using the given Condition:

 

Condition options:

 

  • duplicate - for each duplicate value, selects all such records except one,
  • non-unique - selects records with values that occur more than once,
  • unique - selects records with values that occur only once.

 

Other options:

 

  • Ignore time - Ignore time when comparing values. Compare only dates. Checked by default.

 

Launch the template by choosing a datetime field in the Select pane and then double-clicking the Unique template.  When the template launches we can specify options.

 

 

Unique : duplicate

For each duplicate value, select all records except one.  

 

We start with a table with a single Datetimes field of type datetime.  Some of the values are the same for both date and time, and some of the values have the same date but a different time.  We have used the Layers pane to hide the mfd_id field, for a simpler illustration.

 

 

With the focus on the table window, in the Select pane we choose the Datetimes field and then we double-click the Unique template to launch it.

 

 

In the Unique template we choose duplicate as the Condition option.   We leave the Ignore time box checked, the default.

 

For the Action, we use the default replace selection option.

 

Press Select.  

 

 

In the illustration above, we have Ctrl-clicked the column header for the Datetimes field, to sort the field from lowest value to highest value.  That makes clear how the template selects all duplicates but one for records that have duplicate values in the Datetimes field.   With the Ignore time box checked, the template considers only the date portion of the datetime value.

 

Note that the description of what the duplicate option does is that it selects all duplicates except one record.  It is not a matter of selecting all duplicates except the "first" record or except the "last" record.  It simply picks at random one of the duplicate value records to not select out of records with duplicate values.

 

Unique : non-unique

Select records with values that occur more than once, that is, selecting all records with duplicate values in the target field.

 

We start with a table with a single Datetimes field of type datetime.  Some of the values are the same for both date and time, and some of the values have the same date but a different time.  We have used the Layers pane to hide the mfd_id field, for a simpler illustration.

 

 

With the focus on the table window, in the Select pane we choose the Datetimes field and then we double-click the Unique template to launch it.

 

 

In the Unique template we choose duplicate as the Condition option.   We leave the Ignore time box checked, the default.

 

For the Action, we use the default replace selection option.

 

Press Select.  

 

 

In the illustration above, we have Ctrl-clicked the column header for the Datetimes field, to sort the field from lowest value to highest value.  That makes clear how the template selects all records with duplicate values in the Datetimes field.

 

With the Ignore time box checked, the template only looks at the date part of datetime values to find duplicates.  We can uncheck that box to see how that changes the selection.

 

 

We uncheck the Ignore time box and then press Select.

 

 

The result is that fewer records are selected as duplicates, only those for which both the date and the time are the same.

 

Unique : unique

Select records with values that occur only once.

 

We start with a table with a single Datetimes field of type datetime.  Some of the values are the same for both date and time, and some of the values have the same date but a different time.  We have used the Layers pane to hide the mfd_id field, for a simpler illustration.

 

 

With the focus on the table window, in the Select pane we choose the Datetimes field and then we double-click the Unique template to launch it.

 

 

In the Unique template we choose duplicate as the Condition option.   We leave the Ignore time box checked, the default.

 

For the Action, we use the default replace selection option.

 

Press Select.  

 

 

In the illustration above, we have Ctrl-clicked the column header for the Datetimes field, to sort the field from lowest value to highest value.  That makes clear how the template selects all records that have unique values, that is, that do not have duplicate values, in the Datetimes field.

 

With the Ignore time box checked, the template only looks at the date part of datetime values to find unique values.  

 

If the time part of the value is significant for us, we can uncheck the Ignore time box.

 

 

We uncheck the Ignore time box and then press Select.

 

 

The result is that records with values that differ in date or time are also selected as unique.

 

 

 

See Also

Select - Datetime

 

Select - Datetime: Expression

 

Select - Datetime: Null

 

Select - Datetime: Search

 

Select - Datetime: Statistic

 

Select Reference

 

Select - Expression