Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

- JMP User Community
- :
- Discussions
- :
- To concatenate the workweek numbers to indicate the corresponding cumulative sum...

- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page

Highlighted

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

Sep 8, 2020 8:24 AM
(226 views)

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.

2 ACCEPTED SOLUTIONS

Accepted Solutions

Highlighted

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

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.

Jim

Highlighted

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

Here is a quickie formula that will create your Cum_WW from Workweek

```
If( Row() == 1,
x = "";
hold = "";
);
If( :Qty == :Cum_Qty,
hold = Char( :Workweek );
x = hold;
,
If( hold != "",
x = hold || "_" || Char( :Workweek )
)
);
x;
```

Jim

4 REPLIES 4

Highlighted

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

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.

Jim

Highlighted
##
Re: To concatenate the workweek numbers to indicate the corresponding cumulative sum of quantity from workweek X to Y

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

Thanks Jim. The script is simple and great.

If it is starting with 3 columns 'workweek' , 'Qty' and 'Cum_Qty', is there way to get the column 'Cum_WW'?

Highlighted

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

Here is a quickie formula that will create your Cum_WW from Workweek

```
If( Row() == 1,
x = "";
hold = "";
);
If( :Qty == :Cum_Qty,
hold = Char( :Workweek );
x = hold;
,
If( hold != "",
x = hold || "_" || Char( :Workweek )
)
);
x;
```

Jim

Highlighted
##
Re: To concatenate the workweek numbers to indicate the corresponding cumulative sum of quantity from workweek X to Y

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

Thank you!