Script: Remove prefix of string and output modified column to Excel file

[#1]

Hi everyone. I have been following the advice at String parsing formula  and some other references for writing data to files, mostly applicable to chunk text files but I found the

Save Excel File()

function in the Scripting Index. I don't know what the last parameter means/does? It isn't explained. That is to say, 

Separate Rows for Each Cell Statistic( 1 )

Is this important? It doesn't write anything to any file in the location I have specified whether this parameter is present or not.

Here is a rough outline of my script:

dt = Current Data Table();
c = Words(:ID, "_");
Write(c);
s = c[2] || "_" || c[3] || "_" || c[4] || "_" || c[5];
Write(s);
s << Save Excel File(
	"$DOCUMENTS\temp.xlsx",
	Separate Rows for Each Cell Statistic( 1 )
);

So I'm trying to remove the prefix, and keep the remaining four "word blocks" plus their underscores, which I am adding back via string concatenation manually. See string and variable concatenation for string concatenation "||" reference.

Anyway, no file shows up at $DOCUMENTS\temp.xlsx.

Also the, um, what's it called, terminal, window, log, is giving me the error

Subscript Range in access or evaluation of 'c[2]' , c[/*###*/2]

and I don't know why. No strings or columns are output as a result of the Write() statements I have included.

So basically nothing is working as it should. Square one, I suppose!

Let me know what tips you all can think up.

Cheers,

Mike

[#2]

Hi,

When dealing with strings, I tend to prefer to work with string specific functions (e.g. Substitute (), Munger (), Right (), Word());

Hence, in your case, I would suggest the following formula to remove the first part of the IDS string including the first instance of the "_" character:

Substitute( :IDS, Word( 1, :IDS, "_" ) || "_", "" )

Best,

TS

[#3]

Thanks for the response, @Thierry_S ! I am currently trying your scriptlet out as well as some other things.

This may sound like a super simple thing for most, but I don't actually know what the best way to visualize output for debugging purposes when more than just a single-value variable is involved, i.e. how to pop out a single column into a new data table (using JSL only), or print the column to the log, or something like that.

I don't remember, maybe I've done this before and am just drawing a blank, but I feel kind of silly for asking.

Thanks again!

Mike

[#4]

Also, in general, if you have a function which accepts a parameter or variable, will passing in a column perform the function element-wise to the column?

Mike

[#5]

Also, in general, if you have a function which accepts a parameter or variable, will passing in a column perform the function element-wise to the column?

No. Usually if you need something evaluated for each row of a data table you will want to either create formula column in the data table or use the For Each Row() iterator.

JMP 16 introduced the For Each() function to iterate over JSL data structures: lists, associative arrays, or matrices. 

[#6]

I think Save Excel file is a special case for Categorical Response Analysis Platform Additional Categorical Platform Options . To save an datatable as excel file you can just use << Save(path).

Currently you are trying to save just one string to a excel file, string created from the first row of datatable. Depending how and what you want to save, you have different options, below is one where the :ID column values are replaced with new values with the characters before first _ removed:

Names Default To Here(1);

dt = New Table("Untitled 2",
	Add Rows(3),
	Compress File When Saved(1),
	New Column("ID", Character, "Nominal", Set Values({"1_2_3_4_5_6", "2_3_4_5_6_7", "3_4_5_6_7_8"}))
);

Wait(1);

dt:ID << Set Each Value(
	Substr(:ID, Contains(:ID, "_")+1)
);

dt  << Save("$DOCUMENTS\test.xlsx");

You could also create new column with the new values which is most likely a better approach.

[#7]

This is a great framework; the problem I have identified now is quite simple:

The table I am working with is a subset of rows of the full table. And when I go Column(:ID), it doesn't find the column, i.e.

could not find column{3} in access or evaluation of 'Column' , Column/*###*/(dt:ID)

Which seems really simple but I don't know why. I thought it would be because I am using a subset of the rows, which I do need to do but I can find out if is the case by trying the script on the base table. One moment...

No it seems to be a problem on the full data table as well.

Any advice for "finding" my column?

Michael

[#8]

Add the data table pointer to the column function, make sure it is the table with the column.

Column(dt, :id)

it sounds like there might be multiple tables open, and JMP is trying to use the current table, which is not the one you want.

When you make the subset, you can get the table pointer for the new table:

dtSubset = dt<<subset(...);