401069_en-US

liliconcom

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

Hi everyone. I have been following the advice at https://community.jmp.com/t5/Discussions/String-parsing-formula/td-p/51077 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 https://community.jmp.com/t5/Discussions/string-and-variable-concatenation/td-p/56308 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

 

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

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

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

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.

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

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

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

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

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

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.