347595_en-US

liliconcom

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

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="">9,min(height[Index(Row()-6,Row())])));dt<<RUN formulas="">9,min(height[Index(Row()-7,Row())])));dt<<RUN formulas="">9,min(height[Index(Row()-8,Row())])));dt<<RUN formulas="">9,min(height[Index(Row()-9,Row())])));dt<<RUN formulas=""></RUN></RUN></RUN></RUN></NEW>

 

 

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

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!

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

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?

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?

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?

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="">9,
   min(height[Index(Row()-6,Row())])
  )
 );
);
dt<<RUN formulas="">9,
   min(:height[Index(Row()-6,Row())])
  )
 )
);
show(row6_list);
dt << new column("row6_l", << set values(row6_list));</RUN></NEW>