I tried asking this months ago but I was so lost that my explanation was incoherent. Then I got pulled away on assignment before being able to explain. So here is my hopefully better attempt to explain what I want and what isn't working.
What I'm doing: I'm creating a script that uses a large dataset that will constantly change, though the column names never will. I need to perform a variety of summaries and formulas. My strategy is to do some summary tables to determine which categories of data I have. The example below is a great, simplistic, example of what I'm trying to do. It uses the big class.jmp file and is looking for the sex categories. After it knows the categories, "M", "F" then it will create new columns for each variable category, in this case named :Example-F then :Example-M and within each new column there will be a formula that references other columns and does row calculations referencing the very new column being created.
My problem: I can't get the column references to work, in particular the variable that is calling upon the same column that the formula is being created under. See below
try(close(dt, No save));
try(close(dt_categories, No save));
try(close(dt_data, No save));
clear globals ();
dt = Open( "Path\big_class.jmp" );
//Creates list of gender and replaces names. No issues here.
dt_categories = dt << Summary(
Group( :SEX ),
statistics column name format( "column" ),
output table name( "Gender_Summary" ));
genderlist = dt_categories:sex<<get values;
gendercount = nrows(dt_categories);
dt_data = dt << Split(Split By(:SEX), Split(:HEIGHT), Output Table("Split_Big_Class"), Remaining Columns(Drop All)
For(i=1, i<=gendercount, i++,
//oldcol is created from the genderlist but represents the column names on the new dt_data and my formula will reference them. No issues here.
oldcol = column(genderlist);
//I have to name the new column different than the existing (old) column otherwise jmp will increment the names and it seems like it will really mess up my column name references. No issues here, I don't think.
newcolname = ("Example-"||genderlist);
//Next, I have tried creating the column first so the subsequent formula can reference an already existing column but I don't know if this is necessary.
newcol = dt_data << New Column(newcolname, numeric);
//Next line is a functional formula but doesn't use my variables as reference. You can use to see what I'm trying to do.
//newcolname << Formula(If(Row() == 1, 1 + :F, :Name("Example-F")[Row() - 1,Empty()] + 1));
//Next line is the non-functioning formula because I tried to insert my variables in the above functioning formula. This is where it is throwing errors.
newcolname << Formula(If(Row() == 1, 1 + oldcol, newcol[Row() - 1,Empty()] + 1));
Any suggestions? I'm fairly new to scripting and trying to learn as I go. THANKS!!!!!
Can you explain in words what you want your formula to produce, please?
It looks like you are trying to create values based on previous row data - adding as you go?
If you elaborate on what you want the end result to be - with an example if possible, that would be great.
Sorry, I missed your line showing what you were trying to produce.
dt1 = Open( "$SAMPLE_DATA/Big Class.JMP" );
summarize(categories = by(:SEX));
dt2 = dt1 << Split(Split By(:SEX), Split(:HEIGHT), Output Table("Split_Big_Class"), Remaining Columns(Drop All));
for(i=1, i<=nItems(categories), i++,
dt2 << newColumn("Example-" || categories, character, formula(
If(Row() == 1,
1 + asColumn(categories),
asColumn("Example-" || categories)[Row() - 1] + 1
Thickey, that works perfect. Thank you! It's funny that I had tried the "asColumn" before but the documentation has it as "As Column" but it would always throw errors. Another cool trick you taught me was the summarize bit. @