Syntax: How do I create an index column by ID

[#1]

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

[#2 · Accepted solution]

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

[#3]

Great, that worked perfectly!

Thanks for quick reply.

Have a great day,

Matt

[#4]

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

[#5]

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

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

[#6]

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

Have a great day!