topic Re: Exporting Select Columns to CSV Filtered by Formula Column in Discussions

Exporting Select Columns to CSV Filtered by Formula Column

William_S — Fri, 28 Jun 2019 16:52:30 GMT

Hey everyone,

I'm new to JMP scripts and I'm hoping this may help others too. From the sample data screenshot below (stored as a .JMP file), I'd like to select Material and Month and round Stock to a whole number. I'd then like to filter only records where the rounded Stock calculation is greater than zero. Finally I'd like the results to be saved as a CSV file.

Here is my initial attempt at a JSL version. I'm pretty sure I'm not using the dt expressions correctly (likely among several other things as well) Any help would be tremendously appreciated.

dt = Open( "$SAMPLE_DATA\My JMP Table.jmp" );	

columns( 
		:Material, 
		:Month, 
		New Column("Stock Integer", Numeric, "Continuous", Formula(Round( :Stock,0))  
		)

dt << Select Where Formula(Round( :Stock,0)) >=0 

dt << save( "$Desktop\test.csv" );

Here is an SQL-like equivalent of what I'm trying to do

SELECT 
	[My JMP Table].Material, 
	[My JMP Table].Month, 
	Round([My JMP Table].Stock,0) AS Stock_Integer
INTO 
        test.csv
FROM 
        [My JMP Table]
WHERE 
        Round([My JMP Table].[Stock],0)>0
;

Thanks so much!

Re: Exporting Select Columns to CSV Filtered by Formula Column

Mark_Bailey — Fri, 28 Jun 2019 17:31:04 GMT

I put this together quickly but it should work or be close.

dt1 = Open( "$SAMPLE_DATA\My JMP Table.jmp" );

For Each Row( :Stock = Round( :Stock, 0 ) );

dt1 << Select Where( :Stock >= 0 );

dt2 = dt1 << Subset( Selected Rows( 1 ), Columns( :Material, :Month, :Stock );

dt2 << Save( "$Desktop\test.csv" );

You were on the right path!

Re: Exporting Select Columns to CSV Filtered by Formula Column

William_S — Mon, 01 Jul 2019 16:34:51 GMT

Wonderful, that worked perfectly. Thank you so much!