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

[#1]

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 )
);

[#2]

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.

1. Start your script with Names Default To Here(1);
2. 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()
3. When looping over datatable use For Each Row instead of For as it is its special use case
4. 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)

[#3]

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.

[#4]

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

[#5]

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.

[#6 · Accepted solution]

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

[#7]

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?

[#8]

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)

[#9]

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/h...

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

[#10]

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.