topic Re: JSL code & Summarize help in Discussions

JSL code & Summarize help

SDF1 — Mon, 02 Dec 2024 18:31:50 GMT

Hi JMP community,

JMP Pro v17.

I have a script that had been working last month, but doesn't appear to be working anymore. I have a data table where one column is named :Date (see attached data table). The following script is supposed to summarize the data table by the Year( ) of :Date and generate a list of years for the variable "yrs".

Summarize( dt, yrs = By( Year( :Date ) ) );

For example, it is supposed to return a list of dates like {"2022", "2023", "2024"} and so on, because the Year( ) command is supposed to extract out just the year value from the :Date column, however it doesn't.

If you remove the Year( ) command around :Date and run it like this:

Summarize( dt, yrs = By( :Date ) );

The result is a list of all the individual dates, here's an excerpt:

{"09/29/0263", "01/30/1930", "02/11/1955", "05/03/2002", "08/13/2002", "10/19/2002",
"01/01/2021", "01/02/2021", "01/06/2021", "01/18/2021", "01/30/2021", "02/12/2021",
"02/13/2021", "02/15/2021", "03/26/2021", "03/27/2021", "03/28/2021", "03/30/2021",
"04/23/2021", "04/24/2021", "04/25/2021",}

There are some mistyped dates, but this can be ignored right now.

I don't actually want all the individual dates, I just want a list of all the years. Unfortunately, when I try to execute yr = Year( :Date), then I get a null/empty variable for yr. This ends up not being able to be evaluated by the next By( ) command around it in the original JSL. I don't know if I need

What I find strange is that about a month ago when I was building this code, it seemed to work just fine, but now it's not functioning like it was.

I'm sure I'm missing something simple that's causing this problem, but I can't figure out what it is. Or, I'm thinking about the Summarize( ) and/or how it uses the By( ) group in it.

Thanks for any thoughts/feedback!,
DS

Re: JSL code & Summarize help

Thierry_S — Mon, 02 Dec 2024 19:12:26 GMT

Hi,

You need to Evaluate the Year function as follows:

Eval(Eval Expr(summarize (dt, yr = By(Year(:Date)))));

Best,

Re: JSL code & Summarize help

jthi — Mon, 02 Dec 2024 19:43:05 GMT

I don't think you can do that with Summarize but you could use Summary with transform column if you don't want to create new column to your table

Names Default To Here(1);

dt = Open("$DOWNLOADS/Date.jmp");

dt_summary = dt << Summary(
	Group(Transform Column("Year", Formula(Year(:Date)))),
	Freq("None"),
	Weight("None"),
	Link to original data table(0),
	output table name("Summary of Date grouped by Year"),
	Private
);

years = dt_summary[0, 1];
Close(dt_summary, no save);

Show(years);

Re: JSL code & Summarize help

SDF1 — Mon, 02 Dec 2024 21:23:06 GMT

Hi @Thierry_S and @jthi ,

Thank you both for the quick feedback and ideas. I might have been too quick to mark it as solved though because something strange just happened.

I tried running @Thierry_S 's Eval( Eval Expr( )) wrapped around the summarize command, and it worked when I was testing it on the test data table (the one of just the dates), but as soon as I moved to the original data table, it stopped working, and I get the following error: Column not found in access or evaluation of 'Year' , Year/*###*/(:Date).

Now, when I go back and try to do it on the "fake" data table of dates, I get the error again.

@jthi , Your option is a possibility, but I would need to turn it from a matrix (or 1-D vector into a list of numbers) -- any quick way to do that? I need to do it because I'm using the list of years in an associative array.

Thanks!,

Re: JSL code & Summarize help

jthi — Mon, 02 Dec 2024 21:52:26 GMT

Are the keys integers or strings in your associative array? If they are integers, you can use As List())

Names Default To Here(1);

dt = Open("$DOWNLOADS/Date.jmp");

dt_summary = dt << Summary(
	Group(Transform Column("Year", Formula(Year(:Date)))),
	Freq("None"),
	Weight("None"),
	Link to original data table(0),
	output table name("Summary of Date grouped by Year"),
	Private
);

years = dt_summary[0, 1];
Close(dt_summary, no save);

show(As List(years)); // As List(years) = {263, 1930, 1955, 2002, 2021, 2022, 2023, 2024, 2025};

And if they are strings, I would change the column to character and then take the values

Names Default To Here(1);

dt = Open("$DOWNLOADS/Date.jmp");

dt_summary = dt << Summary(
	Group(Transform Column("Year", Formula(Year(:Date)))),
	Freq("None"),
	Weight("None"),
	Link to original data table(0),
	output table name("Summary of Date grouped by Year"),
	Private
);
Column(dt_summary, 1) << Set Data Type("Character");

years = dt_summary[0, 1];
Close(dt_summary, no save);

show(years); // years = {"263", "1930", "1955", "2002", "2021", "2022", "2023", "2024", "2025"};

Re: JSL code & Summarize help

hogi — Mon, 02 Dec 2024 22:33:34 GMT

I have a script that had been working last month

maybe due to a pre-existing column?

Re: JSL code & Summarize help

hogi — Mon, 02 Dec 2024 22:35:37 GMT

Summarize works with Transform columns:

dt = Open( "$SAMPLE_DATA/Hurricanes.jmp" );
dt << Transform Column( "tmp", Formula( year(:date) ) );
Summarize(years = by (tmp));
//dt << Delete Columns( :tmp );
Show(years)

Don't worry if you forget to delete the transform column, it will be gone after the next "open".

Re: JSL code & Summarize help

SDF1 — Tue, 03 Dec 2024 14:05:16 GMT

Hi @jthi ,

Thanks for the feedback and suggestion. I do need the years as strings because I end up using them later in a combo box, which only takes strings.

Thanks for all the help and troubleshooting!

Re: JSL code & Summarize help

SDF1 — Tue, 03 Dec 2024 14:21:36 GMT

Hi @hogi ,

All the data tables have the same column name :Date, and they're all numeric, continuous columns, so I'm not sure why it worked before and doesn't now. And, what's strange is that it works for the Hurricanes.jmp data table, but it doesn't work for mine. I really don't understand why this happens, but it does.

Anyway, the way that Jarmo suggested works well and doesn't end up being finicky like the Summarize(By( )) command.

Thanks!,

Re: JSL code & Summarize help

hogi — Tue, 03 Dec 2024 14:30:54 GMT

For Hurricanes.jmp, it works due to the column :year.

JMP reroutes the year(:date) to :year ?!?

Re: JSL code & Summarize help

SDF1 — Tue, 03 Dec 2024 15:27:01 GMT

Hi @hogi , maybe. I have no clue, and don't understand why JSL would do something like that. I just tested that theory out, and sure enough, the Summarize command did NOT work because the column :Year was gone. So strange! And, even though the :Date column is a formula in the Hurricanes.jmp file, it doesn't matter. If the formula is removed it works just fine, but if the column :Year is deleted, it no longer works. I don't understand the JSL logic behind that, but that's the way it is.