227179_en-US

liliconcom

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

 

 

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

Thanks BSwid. Let me know how it works.

Re: Select a dynamic number of rows with JSL, selecting rows that meet a conditionThanks 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

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

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

<SPAN class="token comment">// JOIN</SPAN>
DT   <SPAN class="token operator">=</SPAN> <SPAN class="token function">Data Table</SPAN><SPAN class="token punctuation">(</SPAN> TRANSPOSE <SPAN class="token punctuation">)</SPAN> <SPAN class="token operator"><</SPAN><SPAN class="token operator"><</SPAN><SPAN class="token messages"> Join</SPAN><SPAN class="token punctuation">(</SPAN>
	With<SPAN class="token punctuation">(</SPAN> <SPAN class="token function">Data Table</SPAN><SPAN class="token punctuation">(</SPAN> CATEGORY_TABLE <SPAN class="token punctuation">)</SPAN> <SPAN class="token punctuation">)</SPAN><SPAN class="token punctuation">,</SPAN>
	Select<SPAN class="token punctuation">(</SPAN> <SPAN class="token punctuation">:</SPAN>Forecast Material <SPAN class="token punctuation">)</SPAN><SPAN class="token punctuation">,</SPAN>
	SelectWith<SPAN class="token punctuation">(</SPAN> <SPAN class="token punctuation">:</SPAN>Category <SPAN class="token punctuation">)</SPAN><SPAN class="token punctuation">,</SPAN>
	Select<SPAN class="token punctuation">(</SPAN> Select_cols <SPAN class="token punctuation">)</SPAN><SPAN class="token punctuation">,</SPAN>           /// was Select<SPAN class="token punctuation">(</SPAN> <SPAN class="token punctuation">:</SPAN>Row <SPAN class="token number">1</SPAN><SPAN class="token punctuation">,</SPAN> <SPAN class="token punctuation">:</SPAN>Row <SPAN class="token number">2</SPAN> <SPAN class="token punctuation">)</SPAN>
	By Matching Columns<SPAN class="token punctuation">(</SPAN> <SPAN class="token punctuation">:</SPAN>Label <SPAN class="token operator">=</SPAN> <SPAN class="token punctuation">:</SPAN>Category <SPAN class="token punctuation">)</SPAN><SPAN class="token punctuation">,</SPAN>
	Drop multiples<SPAN class="token punctuation">(</SPAN> <SPAN class="token number">0</SPAN><SPAN class="token punctuation">,</SPAN> <SPAN class="token number">0</SPAN> <SPAN class="token punctuation">)</SPAN><SPAN class="token punctuation">,</SPAN>
	Include Nonmatches<SPAN class="token punctuation">(</SPAN> <SPAN class="token number">0</SPAN><SPAN class="token punctuation">,</SPAN> <SPAN class="token number">0</SPAN> <SPAN class="token punctuation">)</SPAN><SPAN class="token punctuation">,</SPAN>
	Preserve main table order<SPAN class="token punctuation">(</SPAN> <SPAN class="token number">1</SPAN> <SPAN class="token punctuation">)</SPAN>
<SPAN class="token punctuation">)</SPAN><SPAN class="token punctuation">;</SPAN>