JSL code & Summarize help

[#1]

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

[#2 · Accepted solution]

Hi,

You need to Evaluate the Year function as follows:

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

Best,

TS

[#3]

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!,

DS

[#4 · Accepted solution]

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"};

[#5]

  I  have a script that had been working last month

maybe due to a pre-existing column?

[#6]

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".

[#7]

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!,

DS

[#8]

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

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

[#9]

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.

[#10]

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!

DS