nav[aria-label="Primary Navigation"] { padding: 0; & ul { list-style: none; width: 100%; display: flex; flex-direction: row; justify-content: start; align-items: start; gap: 30px; padding: 0; & li { margin: 0; } & ul li { list-style: none; } } }

How can get the result of the formula calculated with multiple columns directly from the computation of an array in memory?

lwx228

The requirements for calculation with the data of "Big Class.jmp" and formula are as follows:

Add column "row6" and find the minimum height from row 10 and up to 6 rows;
Add column "row7" and find the minimum value of the height column starting from row 10 and up to 7 rows;
...
How to find the result of "row6" -- "row9" directly by using memory array method?

Thanks!

dt=Open("$SAMPLE_DATA/Big Class.jmp");
dt<<New Column("row6",formula(if(row()>9,min(height[Index(Row()-6,Row())])));dt<<run formulas;Column("row6")<<deleteFormula;

dt<<New Column("row7",formula(if(row()>9,min(height[Index(Row()-7,Row())])));dt<<run formulas;Column("row7")<<deleteFormula;

dt<<New Column("row8",formula(if(row()>9,min(height[Index(Row()-8,Row())])));dt<<run formulas;Column("row8")<<deleteFormula;

dt<<New Column("row9",formula(if(row()>9,min(height[Index(Row()-9,Row())])));dt<<run formulas;Column("row9")<<deleteFormula;

Accepted answers

lwx228

As my English reading ability is limited, I can only use ready-made examples for reference.

The answer is found in this example. Replacing Matrix Values

Thanks Experts!

All comments

jthi

If I understand correctly you want to get results for row6 to row9 directly to arrays without using formulas?

Below is example for row6 using For Each Row with list and same calculation as Formula for column row6 has.

Names Default To Here(1);

dt=Open("$SAMPLE_DATA/Big Class.jmp");
dt<<New Column("row6",
	formula(
		if(row()>9,
			min(height[Index(Row()-6,Row())])
		)
	);
);
dt<<run formulas;
Column("row6")<<deleteFormula;

row6_list = {};
For each row(dt,
	Insert into(row6_list,
		If(row()>9,
			min(:height[Index(Row()-6,Row())])
		)
	)
);
show(row6_list);
dt << new column("row6_l", << set values(row6_list));

txnelson

Names Default To Here( 1 );
dt = current data table();

theHeight = dt:height << get values;

row6=shape([.],40);
row7=shape([.],40);
row8=shape([.],40);
row9=shape([.],40);
row10=shape([.],40);

For(i=11,i<=N Rows(theHeight),i++,
	row6[i]=Min(theHeight[i-6::i]);
	row7[i]=Min(theHeight[i-7::i]);
	row8[i]=Min(theHeight[i-8::i]);
	row9[i]=Min(theHeight[i-9::i]);
	row10[i]=Min(theHeight[i-10::i]);
);

You must read the Scripting Guide so you can start solving these issues by yourself.

lwx228

Thank Jim!

It seems that this method is not the full memory array operation.

lwx228

My actual calculation is:
1. Calculate the minimum value according to the above requirements.
2. Calculate the maximum value according to the same principle.
3. Put the maximum/minimum value of categories 6, 7, 8 and 9 respectively.
4. Select the minimum value of the four ratios
This outputs the minimum ratio of each row to the table.Other intermediate data is not output.

I wrote the code in Excel's VBA.

Sub vba()
ar = Range("d2:d" & Cells(Rows.Count, "d").End(3).Row)
ReDim br(1 To UBound(ar), 1 To 8): ReDim cr(1 To UBound(ar), 1 To 1)
For r = 10 To UBound(ar)
    n = 99: m = 0
    For c = 0 To 8
        If ar(r - c, 1) < n Then n = ar(r - c, 1)
        If ar(r - c, 1) > m Then m = ar(r - c, 1)
        If c > 4 Then br(r, c - 4) = n: br(r, c) = m

    Next

Next
Cells(2, "G").Resize(UBound(br), UBound(br, 2)) = br

For r = 10 To UBound(ar)
    For c = 1 To 4
        If c = 1 Then
            cr(r, 1) = br(r, c + 4) / br(r, c)
        Else
            If br(r, c + 4) / br(r, c) < cr(r, 1) Then cr(r, 1) = br(r, c + 4) / br(r, c)
        End If

    Next
Next
Cells(2, "f").Resize(UBound(br), 1) = cr
End Sub

How do I change this VBA to JSL?

Thanks!

lwx228

As my English reading ability is limited, I can only use ready-made examples for reference.

The answer is found in this example. Replacing Matrix Values

Thanks Experts!