cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 
Try the Materials Informatics Toolkit, which is designed to easily handle SMILES data. This and other helpful add-ins are available in the JMP® Marketplace
Choose Language Hide Translation Bar

Need help with For Loop

I am new to JSL but with the help of this community I was able to write a piece of code to collect air quality data from a URL for a specified pollutant and period of time. Everything works fine in the code except for when there is no data in the URL and then the For Loop breaks. Is there any way to put a condition so the For Loop ignores the table with no data? I am pasting the code below. If you specify 2021 as the 'end_year', the code works fine, but if you specify 2022 as the 'end_year' the code breaks since there is no data in the second half of the year yet (Line 35).

 

Names Default To Here(1);

//************************************INPUT****************************//

parameter= "SO2"; // 1. Pick from "NO2", "OZONE", "PM25HR", "NOX", "NO", "CO", "SO2"//
start_year= 2019; //   2. Specify start year
end_year= 2021; //     3. specify end year


//--------------------------------------------------------------------------------------------------------------

//This URL downloads data from Jan 1st until June 30
dl_url1= "https://www.arb.ca.gov/aqmis2/display.php?sitelist=All&filefmt=csv&fname=" || parameter || "_PICKDATA_^temp_year^-6-30&datafmt=dvde&download=y&first_date=^temp_year^-1-1&param=" || parameter || "&units=001&year=^temp_year^&county_name=--COUNTY--&basin=SC-South+Coast&latitude=--PART+OF+STATE--&report=PICKDATA&order=basin%2Ccounty_name%2Cs.name&submit=All+Sites&ptype=aqd&std15=&o3switch=new&hours=all&statistic=&qselect=Screened&start_mon=1&start_day=1&mon=6&day=30&rows=18";

tableList = {};

For(i = start_year, i <= end_year, i++,
	temp_year= Char(i);
	year_url = Eval Insert(dl_url1);
	
	dt_temp = Open(year_url, columns(
	New Column( "site", Character, "Nominal" ),
	New Column( "date",
			Numeric,
			"Continuous",
			Format( "yyyy-mm-dd", 10 ),
			Input Format( "yyyy-mm-dd" )
		)));
	dt_temp << delete rows( Index( N Rows(dt_temp) - 11, N Rows( dt_temp) ) ); //this code deletes the last 11 rows of data
	Insert Into( tableList, dt_temp << get name );
		);


//This URL downloads data from July 1st 1st until Dec 31
dl_url2= "https://www.arb.ca.gov/aqmis2/display.php?sitelist=All&filefmt=csv&fname=" || parameter || "_PICKDATA_^temp_year^-12-31&datafmt=dvde&download=y&first_date=^temp_year^-7-1&param=" || parameter || "&units=001&year=^temp_year^&county_name=--COUNTY--&basin=SC-South+Coast&latitude=--PART+OF+STATE--&report=PICKDATA&order=basin%2Ccounty_name%2Cs.name&submit=All+Sites&ptype=aqd&std15=&o3switch=new&hours=all&statistic=&qselect=Screened&start_mon=7&start_day=1&mon=12&day=31&rows=18";


For(i = start_year, i <= end_year, i++,
	temp_year= Char(i);
	year_url = Eval Insert(dl_url2);
	
	dt_temp = Open(year_url, columns(
	New Column( "site", Character, "Nominal" ),
	New Column( "date",
			Numeric,
			"Continuous",
			Format( "yyyy-mm-dd", 10 ),
			Input Format( "yyyy-mm-dd" )
		)));
	dt_temp << delete rows( Index( N Rows(dt_temp) - 11, N Rows( dt_temp) ) );
	Insert Into( tableList, dt_temp << get name );
		);
//The following code concat all tables
all_dt= new Table("All " || parameter || " Data");

For( i = 1, i <= N Items( tableList ), i++,
	 Data Table(all_dt) << Concatenate(
		Data Table( tableList[i] ),
		append to first table(1)
	);
	Close( Data Table( tableList[i] ), No Save );
);

// Create Time (PST) Column
Data Table(all_dt) << New Column( "Column 4",
	Numeric,
	"Continuous",
	Format( "Best", 12 ),
	Set Selected
) << Move Selected Columns( {:Column 4}, after( :start_hour ) );

Data Table(all_dt):Column 4 << Input Format( "m/d/y h:m" ) <<
Set Name( "Time (PST)" ) << Format( "m/d/y h:m", 19 ) <<
Set Formula( :date + :start_hour * 3600 );

// Create Month Column
Data Table(all_dt) << New Column( "Month",
	Character,
	"Nominal",
	Formula( Substr( Format( :"Time (PST)"n, "Monddyyyy" ), 1, 3 ) )
) << Move Selected Columns( {:"Month"n}, after( :"Time (PST)"n ) );

// Create Year Column
Data Table(all_dt) << New Column( "Year",
	Character,
	"Nominal",
	Formula( Substr( Format( :"Time (PST)"n, "Monddyyyy" ), 6, 4 ) )
) << Move Selected Columns( {:"Year"n}, after( :"Month"n ) );
1 ACCEPTED SOLUTION

Accepted Solutions
jthi
Super User

Re: Need help with For Loop

You could skip the urls which have 0 rows of data with something like this:

	If(N Rows(dt_temp) > 0,
		dt_temp << delete rows(Index(N Rows(dt_temp) - 11, N Rows(dt_temp)));
		Insert Into(tableList, dt_temp << get name);
	,
		Close(dt_temp, no save);
	);

if you want to stop (break out of) the for loop on first table without data, you can use Break() inside the for-loop:

	If(N Rows(dt_temp) == 0,
		Close(dt_temp, no save);
		break();
	);

 Edit:

Also check out Continue() it might be helpful in some cases

-Jarmo

View solution in original post

2 REPLIES 2
jthi
Super User

Re: Need help with For Loop

You could skip the urls which have 0 rows of data with something like this:

	If(N Rows(dt_temp) > 0,
		dt_temp << delete rows(Index(N Rows(dt_temp) - 11, N Rows(dt_temp)));
		Insert Into(tableList, dt_temp << get name);
	,
		Close(dt_temp, no save);
	);

if you want to stop (break out of) the for loop on first table without data, you can use Break() inside the for-loop:

	If(N Rows(dt_temp) == 0,
		Close(dt_temp, no save);
		break();
	);

 Edit:

Also check out Continue() it might be helpful in some cases

-Jarmo

Re: Need help with For Loop

Many thanks @jthi for the prompt response.