cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 
JMP is taking Discovery online, April 16 and 18. Register today and join us for interactive sessions featuring popular presentation topics, networking, and discussions with the experts.
Choose Language Hide Translation Bar
mpg
mpg
Level II

Syntax: How do I create an index column by ID

Hello JMP Community,

 

I am trying to create a column that indexes rows by an ID column.

In other scripting language, this is accomplished with the following lines.

By ID: gen IDIndex = _n

By ID: gen IDCount = _N

 

I can do the following, but I index and count the whole table

New Column( "IDIndex", formula( Row() ) );
New Column( "IDCount", formula( N Row() ) );

Advice on this would be great!

Very Sincerely,

 

ID IDIndex IDCount
444 1 4
444 2 4
444 3 4
444 4 4
333 1 3
333 2 3
333 3 3
1 ACCEPTED SOLUTION

Accepted Solutions
Jeff_Perkinson
Community Manager Community Manager

Re: Syntax: How do I create an index column by ID

In your formula you need to check the ID value to see if it has changed from the previous row and reset your index if it has.

Try this formula:


     If(:ID != Lag(:ID, 1) | Row() == 1, 1, Lag(:IDIndex, 1) + 1)

Your ID Count column has use IDIndex to get its value:

     Col Maximum( :IDIndex, :ID )

    

The second argument to the Col Maximum function is the By variable.

I hope that helps!

-Jeff

-Jeff

View solution in original post

5 REPLIES 5
Jeff_Perkinson
Community Manager Community Manager

Re: Syntax: How do I create an index column by ID

In your formula you need to check the ID value to see if it has changed from the previous row and reset your index if it has.

Try this formula:


     If(:ID != Lag(:ID, 1) | Row() == 1, 1, Lag(:IDIndex, 1) + 1)

Your ID Count column has use IDIndex to get its value:

     Col Maximum( :IDIndex, :ID )

    

The second argument to the Col Maximum function is the By variable.

I hope that helps!

-Jeff

-Jeff
mpg
mpg
Level II

Re: Syntax: How do I create an index column by ID

Great, that worked perfectly!

Thanks for quick reply.

Have a great day,

Matt

ron_horne
Super User (Alumni)

Re: Syntax: How do I create an index column by ID

Jeff is correct and i will only add that the formula for IDIndex depends on previous sorting.

If the table needs to be re-sorted later you would need to suppress evaluation or delete it.

column (dt, "IDIndex") << suppress eval ( true );

or:

column (dt, "IDIndex") << delete formula;

to produce the column IDCount without sorting you can use the table summery option:

dt << Summary (Group ( :ID );

ron

ms
Super User (Alumni) ms
Super User (Alumni)

Re: Syntax: How do I create an index column by ID

I think the below formula will work for IDIndex independently of the initial sorting of the ID column:

Sum( :ID[Index( 1, Row() )] == :ID )

mpg
mpg
Level II

Re: Syntax: How do I create an index column by ID

Thanks MS, the command you shared does work independently of the sorting.

Have a great day!