To concatenate the workweek numbers to indicate the corresponding cumulative sum of quantity from workweek X to Y

bzanos

I have data table with three columns. 1st column is 'workweek'. 2nd column is 'Qty'. 3rd column is 'Cum_Qty'. 

In 'Cum_Qty' column, it value is ongoing sum of the previous row value in 'Qty' column until meet => 500.

If the row qty is => 500 in , the value will update as it in the 'cum_Qty'. 

 

I need help to write JSL script to create a new column ('Cum_WW') that will concatenate the workweek numbers to indicate the corresponding 'Cum_Qty' is sum from workweek X to workweek Y in order to meet qty => 500. Afterward, delete the some rows.

Refer to attached file and explanation below for details.

 

Examples:

In workweek 2, the qty is 633 (> 500) in 'Qty'column, qty is 633 in 'Cum_Qty' (no sum of previous row values), in 'Cum_WW' will indicates workweek 2.

In workweek 3, the qty is 185 (< 500), it sums 185 (from workweek 3) + 633(from workweek 2) = 818, this value is updated in 'Cum_Qty' column, in 'Cum_WW' will indicate workweek 2_3.

In workweek 4, the qty is 130 (<500), it sums 130 (from workweek 4) + 185 (from workweek 3) + 633(from workweek 2) = 948, this value is updated in 'çum_Qty' column, in 'Cum_WW' will indicate workweek 2_4.

In workweek 5, the qty is 90 (<500), it sums 90 (from workweek 5) + 130 (from workweek 4) + 185 (from workweek 3) + 633(from workweek 2) = 1038, this value is updated in 'Cum_Qty' column, in 'Cum_WW' will indicate workweek 2_5.

and so on......

 

Afterwards, delete row in in 'Cum_WW' 2, 2_3, 2_4 and maintain the row of data in 'Cum_WW' 2_5….and so on….


Thanks for your help.



 

Data1.xlsx

txnelson

Here is a script that follows the steps that can be taken using interactive JMP to solve this problem.  Being able to effectively create scripts in JMP requires a good understanding of what JMP can do interactively.  Good scripts take advantage of the builtin features of interactive JMP.  A good start in learning the ins and outs of JMP can be found through reading the JMP Documents, Discovering JMP, and Using JMP.  These can be found in the JMP Documentation Library

     Help=>JMP Documentation Library

Here is a script that does what you want.  It assumes that it is starting with the 2 columns, Qty and Cum_WW.

Names Default To Here( 1 );
dt = Current Data Table();

dt << New Column( "Cum_Qty",
	formula(
		If( Row() == 1, x = 0 );
		If( :Qty > 500,
			x = :Qty,
			x = x + :Qty
		);
		x;
	)
);

// Turn formula columns into static values
dt:Cum_Qty << delete formula;

dt << New Column( "WW", formula( Word( 1, :Cum_WW, "_" ) ) );

// Create a new column that is a numeric version of the Cum_WW
dt << New Column( "Cum_WW_Num",
	formula(
		Sum(
			Num( Word( 1, :Cum_WW, "_" ) ),
			Num( Word( 2, :Cum_WW, "_" ) ) * .1
		)
	)
);

dt << select where( :WW == "" );
dt << delete rows;

dt << New Column( "select",
	Formula(
		If( :Cum_WW_Num != Col Max( :Cum_WW_Num, :WW ),
			Row State( Row() ) = Selected State( 1 )
		)
	)
);

dt << delete columns( {"WW", "Cum_WW_Num", "select"} );
dt << delete rows;

Please study the script, so that you understand the various items that were used to get to the solution.  If you have questions about any of them, please reply to this post and I will help fill in your understanding.