cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 
JMP is taking Discovery online, April 16 and 18. Register today and join us for interactive sessions featuring popular presentation topics, networking, and discussions with the experts.
Choose Language Hide Translation Bar
BSwid
Level IV

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

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 )
);

 

 

4 REPLIES 4
BSwid
Level IV

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

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

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.

BSwid
Level IV

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

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

Thanks BSwid. Let me know how it works.