topic Re: Select a dynamic number of rows with JSL, selecting rows that meet a condition in Discussions

Select a dynamic number of columns with JSL, selecting columns that meet a condition

BSwid — Thu, 10 Oct 2019 12:48:51 GMT

https://www.jmp.com/support/help/14-2/use-set-selected-to-select-columns.shtml

What's the best way to rewrite something like this?

The intent is that DT is a join of TRANSPOSE and CATEGORY_TABLE.

CATEGORY_TABLE is a one column table with column Category
TRANSPOSE is the result of a data transpose

The trick is that TRANSPOSE doesn't always have the same number of columns. Confusingly, the output of the transpose function names the additional columns Row 1, Row 2, etc.

TRANSPOSE's first two columns are always the same.
Column Row 1 will always exist as the 3rd column.
Column(s) Row 2 and beyond may exist depending on the dataset.

Can I write the Select() function in such a way that the Join() will select at least column Row 1 and then if there are more thru the last column?

// This section is only build to handle two columns named Row 1 and Row 2
DT   = Data Table( TRANSPOSE ) << Join(
	With( Data Table( CATEGORY_TABLE ) ),
	Select( :Forecast Material ),
	SelectWith( :Category ),
	Select( :Row 1, :Row 2 ),   // sometimes there is only one row, sometimes there are more than one rows ******How to handle this?********
	By Matching Columns( :Label = :Category ),
	Drop multiples( 0, 0 ),
	Include Nonmatches( 0, 0 ),
	Preserve main table order( 1 )
);

Re: Select a dynamic number of rows with JSL, selecting rows that meet a condition

BSwid — Wed, 25 Sep 2019 20:27:25 GMT

I tried something like this and got error Column not found {1} in access or evaluation of 'Bad Argument'

Which I imagine is the arguement within Select() within Join().

I thought maybe I could assign a vector of columns to a variable and use the variable as the argument in Select().

// Get a list of the column names
colList = TRANSPOSE  << Get Column Names();
// Starting with column 3, select
For(i=3,i<= N Cols( TRANSPOSE ), i++, COLUMN( TRANSPOSE , colList[i]) << Set Selected(1) ) ;
// Assign those selected columns to a variable
Select_cols = TRANSPOSE  << Get Selected Columns();
//then feed that variable into the Select() withing Join()

// JOIN
DT   = Data Table( TRANSPOSE ) << Join(
	With( Data Table( CATEGORY_TABLE ) ),
	Select( :Forecast Material ),
	SelectWith( :Category ),
	Select( Select_cols ),           /// was Select( :Row 1, :Row 2 )
	By Matching Columns( :Label = :Category ),
	Drop multiples( 0, 0 ),
	Include Nonmatches( 0, 0 ),
	Preserve main table order( 1 )
);

Re: Select a dynamic number of rows with JSL, selecting rows that meet a condition

DonMcCormack — Thu, 10 Oct 2019 14:22:34 GMT

Remove the arguments with "Select" in them:

DT   = Data Table( TRANSPOSE ) << Join(
	With( Data Table( CATEGORY_TABLE ) ),
	By Matching Columns( :Label = :Category ),
	Drop multiples( 0, 0 ),
	Include Nonmatches( 0, 0 ),
	Preserve main table order( 1 )
);

The results will contain all the columns from both tables.

Re: Select a dynamic number of rows with JSL, selecting rows that meet a condition

BSwid — Fri, 11 Oct 2019 13:37:48 GMT

Thanks Don. I'm trying to update someone else's code. I think this doing what we want. Need to grab some time to validate before accepting as solution. Didn't want you to think I didn't see and appreciate your response. Was working with this suggestion yesterday. Thanks!

Re: Select a dynamic number of rows with JSL, selecting rows that meet a condition

DonMcCormack — Fri, 11 Oct 2019 17:59:08 GMT

Thanks BSwid. Let me know how it works.