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

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

lwx228 — Fri, 09 Jun 2023 22:02:57 GMT

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;

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

jthi — Tue, 12 Jan 2021 14:02:00 GMT

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

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

txnelson — Tue, 12 Jan 2021 16:11:09 GMT

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.

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

lwx228 — Wed, 13 Jan 2021 10:58:58 GMT

Thank Jim!

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

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

lwx228 — Wed, 13 Jan 2021 11:00:30 GMT

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!

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

lwx228 — Thu, 14 Jan 2021 13:16:55 GMT

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

The answer is found in this example.

Thanks Experts!