232631_zh-CN

liliconcom

新列中不同列的行累计和

dt = Current Data Table();


// List of column names for the table

col_names = dt << get column names( string );


// Create cumulative sum of each D

looking_for_channel_1 = "D";

For( i = 1, i <= N Items( col_names ), i++,

// search column names for "D"

If( Starts With( col_names[i], looking_for_channel_1 ),

new_col = dt << New Column( Char( col_names[i] ) || ">=",
Numeric,
.................................
);

);

);

各位，

我是一名新的脚本编写者，试图解决当前的问题。 我不太熟悉语法，但我想循环列和行来计算每个工具和行的累积和，并将结果保存在新列中。 我附上了一个显示解决方案的数据表。

 

非常感谢您提供的任何反馈。

BR

马丁

回复：新列中不同列的行累积和

这是另一种方法，只需使用公式列创建新列

Names Default To Here( 1 );

// This JSL assume the table contains only the Tool column,
// followed by the Dnnn columns.  No cummulative columns have been
// created yet
dt = Current Data Table();

totCol = N Cols( dt );

// Loop across the columns creating the new cumulative sums
For( i = 2, i <= totCol, i++,
 Eval(
  Substitute(
    Expr(

     dt << New Column( __newname__, formula( __formula__ ) )
    ),
   Expr( __newname__ ), Column( dt, i ) << get name || ">=",
   Expr( __formula__ ),
    If( i == 2,
     Parse( Column( dt, i ) << get name ),
     Parse(
      "sum( :Name(\!"" || char( Column( dt, i - 1 ) << get name ) || ">=\!"), " || char( Column( dt, i ) << get name )
       || ")"
     )
    )
  )
 )
);

回复：新列中不同列的行累积和

我采取了不同的方法来解决你的问题。对于大量的列和行，循环数据、按照您的建议累积数据将会变得相当慢。使用内置 JMP 函数会快得多。下面的脚本使用内置的 Col Cumulative Sum() 函数将数据转置为高数据表进行处理，然后在处理完成后将数据转置回宽格式。

names default to here(1);
dt=current data table();

// Transform the data into a long table from the current wide table
// to allow for faster processing using the built in column function
//      col cumulative sum()
dtStack = dt << Stack(
 columns( :D300, :D200, :D100, :D50, :D10 ),
 Source Label Column( "Label" ),
 Stacked Data Column( "Data" ),
 Name( "Non-stacked columns" )(Keep( :Tool, :Column 13 ))
);

// Add a new column to insure an alphbetical ordering of the original
// data table
dtStack << New Column( "Row Number",
 Character,
 "Nominal",
 Formula(
  If( Lag( :Tool ) != :Tool,
   counter = 0
  );
  counter = counter + 1;
  result = Substr( "00", Length( Char( counter ) ) - 3 ) || Char( counter )
   || " " || :Label;
 )
);

// Add the Cumulative Sum column
dtStack << New Column( "xx",
 Numeric,
 "Continuous",
 Format( "Best", 12 ),
 Formula( Col Cumulative Sum( :Data, :Tool ) ),
 Set Selected
);

// Transpose the data back into a wide format 
dtFinal = dtStack << Split(
 Split By( :Row Number ),
 Split( :Data, :Name( "xx" ) ),
 Group( :Tool ),
 Remaining Columns( Drop All ),
 Sort by Column Property
);

// Close the nolonger needed stack data table
close(dtStack,nosave);

// Pass across the column names adjusting them back to the desired form
For( i = 2, i <= N Cols( dtFinal ), i++,
 If(
  Word( 1, Column( dtFinal, i ) << get name, " " ) == "Data",
   Column( dtFinal, i ) << set name( Substr( Column( dtFinal, i ) << get name, 10 ) )
 ,
  Word( 1, Column( dtFinal, i ) << get name, " " ) == "xx",
   Column( dtFinal, i ) << set name( Substr( Column( dtFinal, i ) << get name, 8 ) || ">=" )
 )
);