cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 
Browse apps to extend the software in the new JMP Marketplace
Choose Language Hide Translation Bar
WetlandWoman
Level II

How do I generate a numbering column that resets based on other column values?

Hi everyone,

I am trying to generate a column that will count the quadrats (single sample area) for a given site on a particular date. I have a Site Code column and a Date column, as well as Latitude and Longitude columns, where a change in latitude and/or longitude equals a new quadrat for that site. Within each quadrat all species were sampled, meaning each row with the same site, date, latitude and longitude has a new species code. I have been able to create a rank column that resets when these four columns change (i.e. site code, date, latitude and longitude) which gives me a count of the number of species that were found at each quadrat. Is there a way to use any of these columns to auto-number when a quadrat changes (integer starting at 1 and increasing by 1) in a site and resets the numbering when the site and/or date changes. I have attached an example file with a subset of my data, and a column called Quadrat that I manually populated with what I am trying to achieve.

 

Quadrat Screenshot.jpg

 

I am using JMP version 16.2.0. Thank you so much in advance for your help!

2 ACCEPTED SOLUTIONS

Accepted Solutions
jthi
Super User

Re: How do I generate a numbering column that resets based on other column values?

I think you could use something like this:

Col Cumulative Sum(If(Row() == Col Min(Row(), :Date, :Latitude, :Longitude), 1, 0), :Wetland Code)

jthi_0-1662226676297.png

 

-Jarmo

View solution in original post

WetlandWoman
Level II

Re: How do I generate a numbering column that resets based on other column values?

Hi @jthi! This is very nearly what I wanted, thank you so much! I just had to create a concatenate column with Wetland Code and Date (called Sampling Code) to pick up when the site or date changed but the other variable stayed the same and it worked!

 

Col Cumulative Sum( If( Row() == Col Min( Row(), :Date, :Latitude, :Longitude ), 1, 0 ), :Sampling Code )

Thanks so much again for the help

View solution in original post

2 REPLIES 2
jthi
Super User

Re: How do I generate a numbering column that resets based on other column values?

I think you could use something like this:

Col Cumulative Sum(If(Row() == Col Min(Row(), :Date, :Latitude, :Longitude), 1, 0), :Wetland Code)

jthi_0-1662226676297.png

 

-Jarmo
WetlandWoman
Level II

Re: How do I generate a numbering column that resets based on other column values?

Hi @jthi! This is very nearly what I wanted, thank you so much! I just had to create a concatenate column with Wetland Code and Date (called Sampling Code) to pick up when the site or date changed but the other variable stayed the same and it worked!

 

Col Cumulative Sum( If( Row() == Col Min( Row(), :Date, :Latitude, :Longitude ), 1, 0 ), :Sampling Code )

Thanks so much again for the help