topic Re: With JSL, how to split a datatable by distinct values in a column? in Discussions

With JSL, how to split a datatable by distinct values in a column?

Setsuna — Fri, 09 Jun 2023 16:05:40 GMT

Hello, I'm a starter of JSL and I have a question :grinning_face:

For example, I have a data table, and I want to divide (subset) it into several data tables, by the values of B column.

A B C A B C A B C 1 1 1 => 1 1 1 AND 3 2 6 2 1 4 2 1 4 2 2 1 3 2 6 5 2 3 2 2 1 5 2 3

I know how to Subset a data table, or how to select rows according to B values (Select Where), but I don't know how to get a list of all distinct values in column B (just like SELECT DISTINCT statement in SQL) without go through all the rows.

What is the best way to do this work?

Thank you.

Re: With JSL, how to split a datatable by distinct values in a column?

ih — Fri, 06 Jan 2023 13:46:08 GMT

Hi @Setsuna,

I believe you are looking for the 'By' option in Subset:

Names Default To Here( 1 );

dt = Open( "$Sample_data/big class.jmp" );

dtSubs = dt << Subset( All rows, Selected columns only( 0 ), By( :age ) );

//dtSubs is a list of data table references:
dtSubs[1] << Set Name("First table")

Re: With JSL, how to split a datatable by distinct values in a column?

Setsuna — Fri, 06 Jan 2023 14:00:34 GMT

Thank you very much! That's exactly what I want.