How do I conditionally setting cells in a column to missing values

[#1]

After hours of Internet searching, there does not appear to be a clear answer on how to accomplsh what I want to do and, after a number coding attempts, this is clearly one of those areas where JSL differs dramatically from other programming languages. I have CSV files that are used as databases in a custom VBA program for a client. The reason for using CSV files was largely due to making it easy to import the database into JMP to perform periodic analytics without having to create a specialized export feature, as the previous version of the custom tool required. I am building a script to import the database file and perform some preliminary preparation so that the data table has everything in place so that performance analytics may be performed. Most of this is pretty routine and has been accomplished in my JSL script thus far.

The issue I now have is that I have zero values in several places that should actually be missing values. The zeroes resulted from exporting the data from Excel using a data object in VBA. The data in the database file is never on a worksheet, as the custom Excel tool is completely GUI-based, so the data that is actually missing has a default value of 0 in the properties that make up the data object in the code; VBA has no equivalent of missing data for numeric data types.

Thus, what I want to do is replace the zeros in specific columns to missing values based on flags in other columns. For example, if the “Canceled Reads Data” column has ‘False’ in a given row, then all of the columns associated with a canceled reads survey for that row should be missing values, as a canceled reads survey was not conducted for that particular record. Similarly, if the value of the “Survey Canceled” column is ‘True’ then, all of the survey data columns in that row should contain missing values as no survey of any type was conducted for that record. With the zeroes in place, various statistics will be skewed because JMP will include records in analysis that should be ignored.

I have tried two different methods to no avail. The first was using a condition and looping through the columns:

// set canceled data to missing if canceled survey was not performed
If(:Canceled Reads Data == "True",
	{
		For(i = vehiclePackageColumn, i < expiredReadsColumn, i++,
			Column(i) = .
		)
	}
);

Part of the problem here appears to be that the column references—vehiclePackageColumn and expiredReadsColumn—are not column numbers because using vehiclePackageColumn = Column("Vehicle Packages") does not return the column number for the “Vehicle Packages” column. In fact, I could not find any information on how to capture the column index (number) for columns using JSL.

The second method was based on something I saw in another forum:

cancelSurveyStatus = Column(inboundDataTable, "Canceled Reads Data") << Get Values;
dataMissing[Loc(canceledSurveyStatus == "False")] = .;
Column(inboundDataTable, "Canceled Reads") << Set Values(dataMissing);

Any help with this would be greatly appreciated.

[#2 · Accepted solution]

I would think that this would work:

dt = current data table();

For( i = your starting col number, i <= your ending col number, i++,

    column( dt, i ) [ dt << get rows where( :Canceled Reads Data == "True")] = .;

);

[#3]

Yes! 

That seemed to solve the primary issue of getting missing values into a range of columns based on the state of flags in specific columns, Jim. So, the only reminaing problem is if there is a way to capture column indices. My data table is rather large and having to manaully count columns as I did in my test of your suggested code is tedious and prone to error; this particular data table has 73 columns. Lacking a means to determine the column number of named columns in JSL is a serious omission, if only for operations such as this.

[#4 · Accepted solution]

This is an easy one:

colnames = dt << get column names(string);

startindex = contains(colnames, "The name of your starting column");

endindex = contains(colnames, "The name of your ending column");

[#5]

Thanks Jim,

Sorry for the delayed response, but I was offsite with a client yesterday. I will try your suggested solution when I get a chance today.

[#6]

Tried it. Works great. Thanks again, JIm.