Turn on suggestions

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

- JMP User Community
- :
- Discussions
- :
- Re: Formula for Number of Unique Categories in Column

- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page

Highlighted

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

Apr 19, 2017 1:53 PM
(8479 views)

I am looking for the column formula to provide me with the count of unique categories in a column (maybe based on another columns categories). For instance, if a column has

A

A

B

B

B

C

then I would like to get back the value 3.

Thanks

1 ACCEPTED SOLUTION

Accepted Solutions

Highlighted

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

Try this formula.......of course, swap out the :Flow for the column you want to count the categories for

```
Summarize( x = by( :FLOW ) );
N Items( x );
```

If the data table is really large, you may want to use this more efficient version

```
If( Row() == 1,
Summarize( x = by( :Flow ) );
y = N Items( x );
);
y;
```

Jim

9 REPLIES 9

Highlighted
##

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

Re: Formula for Number of Unique Categories in Column

I think I remember the Summation formula being used for this, but my memory fails me.

Highlighted

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

Try this formula.......of course, swap out the :Flow for the column you want to count the categories for

```
Summarize( x = by( :FLOW ) );
N Items( x );
```

If the data table is really large, you may want to use this more efficient version

```
If( Row() == 1,
Summarize( x = by( :Flow ) );
y = N Items( x );
);
y;
```

Jim

Highlighted
##

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

Re: Formula for Number of Unique Categories in Column

This is PERFECT! Thank you Jim!

Highlighted
##

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

Re: Formula for Number of Unique Categories in Column

I see how this would work for a script to create a column, but I've created a datatable that I update from a database and I'd like to have a column in that table with a formula to calculate the number of unique categories. Is there a way to do that directly in a column formula instead of though a script?

I found a hack using using Def() and Col Sum but it only works because i'm looking at a sorted field that's a unique id with numbrers. It doesn't work for actual text variables. Is there a better solution for that?

Here is the hacked formula:

Col Sum( Dif( :customer_id ) > 0 )

Thank you,

Aron

Highlighted
##

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

Re: Formula for Number of Unique Categories in Column

The stated formula from my last response should do what you want.

```
If( Row() == 1,
Summarize( x = by( :Customer_ID) );
y = N Items( x );
);
y;
```

Just cut and paste this into the formula editor

Jim

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

Re: Formula for Number of Unique Categories in Column

Highlighted
##

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

Re: Formula for Number of Unique Categories in Column

That worked great! I was thrown off by the red underline, I thought that meant it wasn't going to work.

I'm not actually sure I've ever tried pasting a formula directly in the column before, I thought I had to rebuild it using the explicit formula column functions... this is a huge improvement. Thank you so much for the quick reply!

Aron

Highlighted
##

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

Re: Formula for Number of Unique Categories in Column

Mr. Nelson,

Can you describe in detail what this function is doing? Not sure I understand the "by" function.

Thanks,

Martin

Highlighted
##

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

Re: Formula for Number of Unique Categories in Column

"By" is a fairly global concept in JMP. If you run the Distribution Platform, and specify a "By" variable it will generate a separate Distribution for each of the different values of the "By" columns. The same is for the Summarize() function. Here is a quote from the JSL Syntax Reference for the Summarize() function.

Help==>Books==>JSL Syntax Reference

"Using a BY variable changes the output from single values for each statistic to a list of values for each group in the BY variable."

Good documentation along with an example can also be fount in the Scripting Index

Help==>Scripting Index==>Functions==>Statistical==>Sumarize

or

Help==>Scripting Index.........search on Summarize

Jim