Showing results for 
Show  only  | Search instead for 
Did you mean: 
Choose Language Hide Translation Bar
Level IV

count unique

Hi, I have a table with transaction data like the below.


transactionID, individualID, product

1, a, apple

2, a, orange

3, b, apple

4, b, banana

5, c, orange


I want to do in tabulate, not using script, to count how many unique individualID by product. 

Does anyone know how to do this?

I can do summary table using individualID and product first, and then do a tabulate on it to get the uniqueID count by product, but this is 2 steps. Wondering is there a way we can do it in tabulate just one step cause i constantly need to do this for many different groups? 

Thank you!

Super User (Alumni) ih
Super User (Alumni)

Re: count unique

Yet another option: you could also make a summary column right in the tabulate function:











Names default to here(1);

dt = Open("$Sample_data/big");

dt << Tabulate(
	Show Control Panel( 0 ),
	Add Table(
		Column Table(
			Analysis Columns(
				Transform Column( "Age Count", Formula( Col Sum( 1, :age ) ) )
			Statistics( Mean )
		Row Table( Grouping Columns( :age ) )






Level XI

Re: count unique

Is there a shortcut for N categories or count unique in the right click New Formula Column menu?
I guess there is one (as it is needed so often), but I couldn't find it - what's the name?

NB: not all of the replies here referred to N categories or count unique  - some are more like N rows per unique entry.


Super User

Re: count unique

There is not a right click solution to this issue, as far as I know.  The old school way that I approach this is:

Summarize( bygroup = by( :age ) );
UniqueCount = N Items( bygroup );


Level XI

Re: count unique

Thanks Jim

For my application I need a function which uses the Group By option of the right click New formula Column menu to calculate and distribute the respective values - similar to Col Number (Count), but just counting unique/distinct entries.


There is nothing like 

N categories(column, bygroups)

- or a an option just count unique values for the Col Number function?

Big-Class application case:
A user want to calculate the percentage of age groups, with mean height > 60, separately for male and female. Then  N categories could be a nice way to do this:

PercentTallAgeGroups = N Categories(if(Col Mean(:height,:age, :sex) > 60,:age, .), :sex) / N Categories(:age, :sex)*100 

More reasonable application cases:
- ratio of defects per measured wafer for several lots (for a data set with chip-fine data)

- ratio of cities with >0.1% Covid mortality for several countries



Generate a Summary Table with the needed by groups and merge it back into the main table.

Level XI

Re: count unique

If other users are interested as well to have N Categories not just in the Tables/Summary Platform, but also via JSL ...
  - here is the wish:
new JSL function: Col N Categories 

Level XI

Re: count unique