- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Get Direct Link
- Report Inappropriate Content
How to fill sequential numbers (starting from 1) in a column when identical items appear in another column?
Lets say I have a two column data. The first column partID, second column is a measured value for that part. Each part is measured N times so that partID column has rows with the ID appearing N times in the table until the next partID apepars. I want to create a new column with number of measurement repeats/Runs for a given part.
So, for identical partIDs appearing in the partID column, I want to fill the Runs column with sequential numbers starting from 1.
So if part A is measured 11 times, the Runs column goes gets row entries from 1 to 11. Next part B is measured 9 times, the Runs column goes has row entries 1 to 9 and so on.
How can I automate this via JSL?
Accepted Solutions
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Get Direct Link
- Report Inappropriate Content
Re: How to fill sequential numbers (starting from 1) in a column when identical items appear in another column?
Col Cumulative Sum with value of 1 should work with current JMP versions
Names Default To Here(1);
dt = New Table("Untitled",
Add Rows(10),
Compress File When Saved(1),
New Column("Column 1",
Character,
"Nominal",
Set Values({"A", "A", "A", "A", "B", "B", "C", "C", "C", "D"})
)
);
dt << New Column("Run", Numeric, Ordinal, Formula(
Col Cumulative Sum(1, :Column 1)
));
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Get Direct Link
- Report Inappropriate Content
Re: How to fill sequential numbers (starting from 1) in a column when identical items appear in another column?
As long as the partIDs are grouped as you stated, this will work.
If(
Row() == 1, 1,
Lag( :partID ) == :partID, Lag( :Runs ) + 1,
1
)
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Get Direct Link
- Report Inappropriate Content
Re: How to fill sequential numbers (starting from 1) in a column when identical items appear in another column?
@mmarchandTSI solution is a fine solution. But as usual, in JMP there are multiple ways to get to the solution. Here is an alternative solution
Col Cumulative Sum( 1, :partID )
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Get Direct Link
- Report Inappropriate Content
Re: How to fill sequential numbers (starting from 1) in a column when identical items appear in another column?
Col Cumulative Sum with value of 1 should work with current JMP versions
Names Default To Here(1);
dt = New Table("Untitled",
Add Rows(10),
Compress File When Saved(1),
New Column("Column 1",
Character,
"Nominal",
Set Values({"A", "A", "A", "A", "B", "B", "C", "C", "C", "D"})
)
);
dt << New Column("Run", Numeric, Ordinal, Formula(
Col Cumulative Sum(1, :Column 1)
));