Choose Language Hide Translation Bar
Highlighted
owiuser
Community Trekker

Creating a data subset based on row order

I have a table of bird survey data collected at numerous stations. Most stations have had multiple surveys conducted on different dates. So my table has multiple rows for each station, each of these rows having information for the different surveys. I've sorted my table by station and then by survey date.  Now I'm trying to create a subset of this table in which there is one record (row) per station and when there are multiple records (rows) for a given station, I want to select the first row in the table.  I've been trying the subset and data filter features without any success.   Any ideas?

I've attached the table.   Station ID is Column ST_CN; survey date is Column MS_DA. 

0 Kudos
1 ACCEPTED SOLUTION

Accepted Solutions
ms
Super User ms
Super User

Re: Creating a data subset based on row order

You can use Summary (group by ST_CN) and then Update the Summary table with origiginal table (matching columns ST_CN). Update by matchng columns seems to use the first match (row) in case of multiple instances.

Can also be done with a script:

(Data Table( "sttn_survey_join2_unocc" ) << summary( group( :ST_CN ) )) <<

Update(

          With( Data Table( "sttn_survey_join2_unocc" ) ),

          Match Columns( :ST_CN = :ST_CN )

)

View solution in original post

0 Kudos
2 REPLIES 2
ms
Super User ms
Super User

Re: Creating a data subset based on row order

You can use Summary (group by ST_CN) and then Update the Summary table with origiginal table (matching columns ST_CN). Update by matchng columns seems to use the first match (row) in case of multiple instances.

Can also be done with a script:

(Data Table( "sttn_survey_join2_unocc" ) << summary( group( :ST_CN ) )) <<

Update(

          With( Data Table( "sttn_survey_join2_unocc" ) ),

          Match Columns( :ST_CN = :ST_CN )

)

View solution in original post

0 Kudos
owiuser
Community Trekker

Creating a data subset based on row order

The script worked perfectly.  Thank you!

0 Kudos