How Do I create a Column formula for N Categories?I would like to create a column formula that provides a count of the number of distinct values in a column. I can use the Column viewer, Show summary function in JMP 15 but I want to have that value in a column for use in other calculations.
For example in the Big Class Table - N Items in column "Sex" should show 2 as 2 distinct values.
Re: How Do I create a Column formula for N Categories?Thank you, @txnelson & @PatrickGiuliano .
Fairly new to JMP, still learning. Good to know about the Col Number() operator.
I have attached a sample of my data set. I am looking for the unique number of tools per week per year. Each tool could have multiple rows per week. I put an ideal outcome column in the set.
Thanks again for your help!
Re: How Do I create a Column formula for N Categories?Thank you Jim!
@CTrahon0526 If you're new to JMP, here's a bit more elaboration on what Jim shared.
You can put the Col Number() function in a column formula and compute it directly in the data table.
I'm attaching a mock example jmp data table.
First, I created a "Year Week" column by using an Instant Column Formula in JMP. Right Click on the time column, New Formula Column > Date Time > Year Week.
Then I created a "Count by Year Week" column with the following column formula in it:
Col Number(:time, :Year Week)
Re: How Do I create a Column formula for N Categories?The function is
Col Number(<:column Name>)
See the Scripting Index for details
Re: How Do I create a Column formula for N Categories?
@PatrickGiuliano ,
Is there a way to get the N Items by Variable?
I am trying to find the unique N values per Year per Work Week. Thanks.
Re: How Do I create a Column formula for N Categories?Thanks @txnelson you're the man!
@patricia_roth and I also came up with the following ode to the gracious support from some of the other JMP team members.
Summarize( x = by( :sex ) );
N Items( x );
very much the same but a few less lines.
Re: How Do I create a Column formula for N Categories?Here is the formula that I would use:
If( Row() == 1,
Summarize( Current Data Table(), bygp = by( :sex ) );
n = N Items( bygp );
);
n;
Re: How Do I create a Column formula for N Categories?I would not do what you are asking for in a formula. Rather, I would:
- Click on Tables=>Summary and create a summary data table, grouping by Year, WW and ToolID
- Using the newly created summary data table, once again, click on Tables=>Summary and create a new summary data table by grouping on just Year and WW. The new table's column called # Rows will give you the number of unique toolIDs per year/WW.
- Next, click on the original data table, and then using the Tables pull down menu once again, select Update. This will copy the data from the second summary table into the original table. In the dialog box for the Update,
- Select the table to update from(the second summary table)
- Select to Match Columns
- Select Year from each table and click on Match
- Select WW from each table and click on Match
- Click on OK
The data will now be in your original data table
Re: How Do I create a Column formula for N Categories?@CTrahon0526, Jim's solution is beautiful! And it teaches you a lot about Table operations in JMP. I agree that a Column formula in this case is very tricky. Tables Summary 2X is a very elegant solution. Then "updating back" the results to the original data table using Tables > Update. That last step can kind be done interactively (by literally dragging and dropping the column from the second summary table back to the first, and finally to the main table) instead of using Tables > Update, but the solution using Update is more robust (reproducible) if you are going to be doing this calculation on many tables. But this kind of gives you a sense of how interactive JMP is as GUI-based tool.
Re: How Do I create a Column formula for N Categories?The drag & drop is very cool.
Nevertheless, there is a HUGE disadvantage o this approach:
it's not dynamic. If data is added to the table, the entries will be wrong.
This is THE benefit of a column formula: The values will be always correct.
Astonishing that there is no New Column Formula / JSL command Col N Categories ( ) available in JMP.
Re: How Do I create a Column formula for N Categories?here is a wish to add such a functionality
... to JSL
... to New Column Formula
... to Graph Builder
Col N Categories
I guess there are only a few JMP users in the world who don't need it ; )
Most JMP users will be excited to see such a functionality in a future version of JMP (... along with dense ranking )
Re: How Do I create a Column formula for N Categories?[removed] didn't show up at the right position.
Re: How Do I create a Column formula for N Categories?A little late to the thread, but I was looking for a solution to the same problem. The formula below in @CTrahon0526 's datatable reproduces the values in "Unique Tool IDs Per Work Week Per Year" column
Col Sum( Col Cumulative Sum( 1, :Year, :WW, :Tool ID ) == 1, :Year, :WW )
Re: How Do I create a Column formula for N Categories?Nice, I can use this a lot - so, now I have:
Names Default To Here( 1 );
Try(dt = CurrentDataTable(), stop());
updateLBox = Function( {t},
(t << sib) << setitems( (t << topparent)[listboxbox( 1 )] << getselected )
);
nw = New Window("count unique entries",
<< Type("Modal Dialog"),
Border Box( Left( 10 ), Right( 10 ), bottom( 5 ),
V List Box(
Lineup Box( N Col( 1 ), spacing( 10 ),
// Text Box( "XG Boost Specification" ),
// colistbox only necessary if the script is standalone
H List Box(
Panel Box( "Select Columns", Col List Box( all, nlines( 15 ) ) ),
Panel Box( "Cast Selected Columns into Roles",
Lineup Box( N Col( 2 ),
Button Box("Unique",
<<SETFUNCTION></SETFUNCTION>... available at a single mouse click : )
Re: How Do I create a Column formula for N Categories?to get the next level of interactivity - please vote here:
Transform Columns - as comfortable as Summary Statistics?
e.g. to show the number of car makers - correct values for individual years - and split by door size:
... there may be more useful applications : )
