topic Re: Beginner trying to Write Script / How to iterate within the formula function? in Discussions

Beginner trying to Write Script / How to iterate within the formula function?

bub — Fri, 28 Feb 2025 03:33:09 GMT

HI, I am a beginner at script writing in any language. I am currently meandering through what I thought would be a simple practice task. I am using JMP18. My goal is to automate a routine operation that I do with growing datasets over time. The routine operation is to first determine the time scale, and then show the change in data over that time scale for large datasets. The basic way I am approaching this is creating new columns to fill in all the initial data and using formulas on more columns to get differences. If someone has a better suggestion, I'd appreciate it. Below is where I am at:

Essentially, I have a set of data and I want to achieve the following.

1) I am looking to take the date and time columns, bring them together into a single column, and then I find the minimum, and use that to create a starting date. I can then subtract the times to see the difference in time between data. This was all simple enough with the formula() functions. Everything here works great.

2) I want to do something similar to several of the data columns, but I cannot do what I did before to find the initial value. The initial data is determined by the minimum time found in what I did before for every unique item in a third column (called Unique_Fruit in the code below). For the sake of this forum, for each unique_fruit, i want to create a column that contains the initial values of data for that fruit so i can then quickly calculate the changes from the actual data column.

My current JSL script is below. I have a dummy code formula where I haven't figured out what to do. It is the steps where I used the If function, found the times equal each other and set the value for only those cells. My original thought was to try using a for loop and iterate in the formula, but that did not seem to work.

DataTable = Current Data Table();  

New Column( "Date Number Format", Numeric, "Continuous" );

For( i = 1, i <= N Rows( DataTable ), i++,
    DataTable:Date Number Format[i] = Num( DataTable:Key_Date[i] );
);

New Column( "Time Number Format", Numeric, "Continuous" );

For( i = 1, i <= N Rows( DataTable ), i++,
    DataTable:Time Number Format[i] = Num( DataTable:Key_Time[i] ); 
);

New Column( "Current_Time", Numeric, "Continuous" );

For( i = 1, i <= N Rows( DataTable ), i++,
    DataTable:Current_Time[i] = DataTable:Date Number Format[i] + DataTable:Time Number Format[i]; 
);

New Column( "Unique_Fruit", Character, "Nominal" );

For( i = 1, i <= N Rows( DataTable ), i++,
    DataTable:Unique_Fruit[i] = DataTable:Fruit[i] || "-" || DataTable:Type[i];  
);

New Column( "Starting_Date_Number_Format", Numeric, "Continuous", 
    Formula( Col Minimum( :Current_Time, :Unique_Fruit ) )
);

New Column( "Minutes", Numeric, "Continuous", 
    Formula( Date Difference( :Starting_Date_Number_Format, :Current_Time, "Minute", "fractional" ) )
);

New Column( "Days", Numeric, "Continuous", 
    Formula( Date Difference( :Starting_Date_Number_Format, :Current_Time, "Day", "fractional" ) )
);

New Column( "Weeks", Numeric, "Continuous", 
    Formula( Date Difference( :Starting_Date_Number_Format, :Current_Time, "Week", "fractional" ) )
);

New Column( "Months", Numeric, "Continuous", 
    Formula( Date Difference( :Starting_Date_Number_Format, :Current_Time, "Month", "fractional" ) )
);

New Column( "Initial_Taste", Numeric, "Continuous", 
    Formula( 
        If( :Current_Time == :Starting_Date_Number_Format, :Key_Taste, . ) 
    )
);

For( i = 1, i <= N Rows( DataTable ), i++,
    If( DataTable:Current_Time[i] == DataTable:Starting_Date_Number_Format[i], 
        DataTable:Initial_Taste[i] = DataTable:Key_Taste[i];
    );
);

New Column( "Delta_Taste", Numeric, "Continuous", 
    Formula( :Key_Taste - :Initial_Taste )
);

New Column( "%_Change_Taste", Numeric, "Continuous", 
    Formula( ( :Delta_Taste / :Initial_Taste ) * 100 + 100 )
);

Re: Beginner trying to Write Script / How to iterate within the formula function?

jthi — Fri, 28 Feb 2025 05:02:32 GMT

Can you provide example dataset + column with correct answer? Most likely you can get the initial values using something like this, but you have to modify the comparison as this one gets first value for each category

Col Cumulative Sum(If(Row() == Col Min(Row(), :age), :height, .), :age)

where :age is unique_fruit and :height is your value column. You can also utilize Lag() and variables.

There are also some changes I would do to your script to make it a bit more robust.

Start your script with Names Default To Here(1);
As you have reference to a datatable use it
1. DataTable << New Column(...
2. I would also consider changing DataTable to dt or something similar as JMP has function called Datatable()
When looping over datatable use For Each Row instead of For as it is its special use case
When you wish to have new columns in JMP table without having formulas you have few options:
1. Use New column + Formula + run formulas + delete formula combination
2. Create new column and loop over the data table (like you are doing)
3. Then there is a bit finnicky method of using << Apply Formula but I would keep to those first (usually I use the first one)

Re: Beginner trying to Write Script / How to iterate within the formula function?

txnelson — Fri, 28 Feb 2025 05:20:55 GMT

Welcome to the Community.

I agree with Jarmo, a sample data table.

I also suggest that you provide a mocked up example of what your expected results are.

Re: Beginner trying to Write Script / How to iterate within the formula function?

bub — Fri, 28 Feb 2025 05:51:37 GMT

Thank you for the suggestion. I am attempting a mock dataset and what I currently do manually. To set the initial values, I look at each unique item and call for the data from that cell and populate the whole column so I can then move on to the next part of the calculation. I'll try updating the rest of the script with your suggestions as well, but the main issue is not solved just yet I think for me (or I I haven't understood just yet.)

Re: Beginner trying to Write Script / How to iterate within the formula function?

hogi — Fri, 28 Feb 2025 06:47:48 GMT

hi @bub , please have a look at https://marketplace.jmp.com/appdetails/Normalization+GUI

It facilitates the task to normalize data by an initial value (subtract or divide) - per group.

Re: Beginner trying to Write Script / How to iterate within the formula function?

jthi — Fri, 28 Feb 2025 06:51:08 GMT

It wasn't solved as it generally takes more time to create mockup data than answer the correct question. You should be able to utilize something like this for initial values

Col Min(If(:Date Number Format == Col Min(:Date Number Format, :Unique_Fruit), :Key_Taste, .), :Unique_Fruit)

This isn't the only way of building these, but something like this does work quite often

Re: Beginner trying to Write Script / How to iterate within the formula function?

bub — Fri, 28 Feb 2025 07:07:35 GMT

Thank you very much. Just to clarify, I wasn't trying to put blame on your first response, more so that I was trying to implement it on a few iterations and thought I was still missing something. I should have kept going because I probably would have ended up at the answer you showed here (eventually), but as I said earlier I am new to scripting these things so it is slow going.

Out of curiosity, why have the "." in the else?

Re: Beginner trying to Write Script / How to iterate within the formula function?

jthi — Fri, 28 Feb 2025 07:20:49 GMT

Missing value is there to attempt to "force" the Col Min to have only one option to pick from as it will ignore missing values.

You can break this type of formulas into multiple parts in attempt to understand them better (sometimes they can still be very cryptic)

//col1
Col Min(:Date Number Format, :Unique_Fruit)

//col2
:Date Number Format == :col1

//col3
If(:col2, :Key_Taste, .)

//col4
Col Min(:col3, :Unique_Fruit)

Re: Beginner trying to Write Script / How to iterate within the formula function?

hogi — Fri, 28 Feb 2025 08:30:04 GMT

In future, tasks like this one will get super easy:
https://community.jmp.com/t5/JMP-Wish-List/Group-by-sooo-useful-how-about-Interpolate/idc-p/828679/highlight/true#M6896

Together with Col Interpolate, many new functions (and functionalities) will be added to the Col aggregations :)

Re: Beginner trying to Write Script / How to iterate within the formula function?

bub — Fri, 28 Feb 2025 21:42:41 GMT

Hey, I had an additional thought as I am playing around with this. If I wanted to do something closer to what I originally thought I had to do when I posted this, how is it done? Where I am at is writing a variable that stores a list and then trying to have the code iterate through that list to populate the formula. Instead of populating the variable remains inside the formula rather than updating to one of the list values. Is that something that can be done within the formula function?

Re: Beginner trying to Write Script / How to iterate within the formula function?

bub — Fri, 28 Feb 2025 21:44:14 GMT

Thank you for the explanation here. I am playing around with it as I continue on this small project for myself and seeing where I can take it.

Re: Beginner trying to Write Script / How to iterate within the formula function?

jthi — Sat, 01 Mar 2025 05:49:10 GMT

You can get a list of values/collect a list of values inside a formula but you have to be just careful how you update the values (using As Constant or If(Row() == 1,...) can help). Also you might want to avoid loops inside formulas as they do trigger quite often.

You could use Lag() but this would require your data to be sorted (can be done without sorting, but it can quickly get complicated) but usually the idea is something a bit like this

If(Row() == 1,
	init_taste = :Key_Taste
);

If(:Unique_Fruit != Lag(:Unique_Fruit),
	init_taste = :Key_Taste;
);

init_taste;

Do note that I did sort the data by Date Number Format and Unique_Fruit.