Create datatable by counting batches in table

[#1]

Hi JMP Community

I have a dataset that is only partially filled out with batch numbers.

My goal is to make a dataset containing one row pr. batch.

I can calculate how many rows are needed by comparing the first and last batch numbers.

As seen in the example I would need a dataset with 30 rows as the first batch no. is 7 and the last is 37.

In the batch name, the 3 first and 2 last digits should be ignored.

In my mind, this should be straight forward, but I am stuck…

I would highly appreciate getting unstuck!

Thanks

Christian

[#2]

Hmmm I don't understand your query?  You have multiple rows with identical information (first 3, and rows 4 & 5), you are missing the rows with 21-23, 25-26, 29?  Also the first 6 digits are identical.

What exactly do you want to do?  You can always right click on the column and use recode.

[#3 · Accepted solution]

Something like this?  It creates a new table with a single row for each batch, instead of multiple rows and fills in the missing batches.

Names Default To Here( 1 );
Batch_vals = Associative Array( :Batch ) << Get Keys;
prefix = Left( :Batch[1], 3 );
suffix = Right( :Batch[1], 2 );
rootlength = Length( Batch_vals[1] ) - Length( prefix ) - Length( suffix );
Batch_nums = Transform Each( {v, i}, Batch_vals, Num( Substitute( v, {prefix, suffix}, "" ) ) );
Min_batch = Min( Batch_nums );
Max_batch = Max( Batch_nums );
Batches = Min_batch :: Max_batch;
dt = New Table( "Batches", New Column( "Batch Number", numeric, continuous, Set Values( Batches ) ) );
Eval(
	Eval Expr(
		dt << New Column( "Batch", character, Formula( Expr( prefix ) || Right( Char( :Batch Number ), rootlength, "0" ) || Expr( suffix ) ) )
	)
);
dt:Batch << Delete Formula;

[#4]

Thanks for the reply @mmarchandFSLR! 

Your script works perfectly!

Your solution contains some prompts/scripts that are new to me, e.g., Associative Array, Transform Each, Eval, Eval Expr.

I am looking forward to digging into how your solution works! 

While writing this post, I bragged about how incredible the JMP community is and was not disappointed! 

cheers, Christian

[#5]

Simple explanation of some of those functions and an important alteration:

Names Default To Here( 1 );
//An associative array is a dictionary or hash map that links keys to values.  Each key must be unique.
//Using a single column as the argument for the array gives us the unique values of the column with each value set to 1
//The <<Get Keys message then yields the keys only, so Associative Array( :Column ) << Get Keys is a way to get unique column values
//Jarmo's example of Summarize() does the same thing and is probably a better method, but the array method works on lists, too
Batch_vals = Associative Array( :Batch ) << Get Keys;
prefix = Left( :Batch[1], 3 );
suffix = Right( :Batch[1], 2 );
rootlength = Length( Batch_vals[1] ) - Length( prefix ) - Length( suffix );
//Transform Each performs the same action on each item in a list, matrix, or associative array and returns the transformed values in the same container
//I should have done this differently to avoid unintended substitutions, since a batch value that matched the prefix or suffix would be deleted.
//Batch_nums = Transform Each( {v, i}, Batch_vals, Num( Substitute( v, {prefix, suffix}, "" ) ) );
Batch_nums = Transform Each( {v, i}, Batch_vals, Num( Substr( v, Length( prefix ) + 1, rootlength ) ) );
Min_batch = Min( Batch_nums );
Max_batch = Max( Batch_nums );
Batches = Min_batch :: Max_batch;
dt = New Table( "Batches", New Column( "Batch Number", numeric, continuous, Set Values( Batches ) ) );
//Eval() always evaluates the code inside
//Eval Expr() evaluates all expressions inside before running the code
//When setting a column formula based on the value of a variable, I wrap that variable inside of Expr() and then use Eval( Eval Expr() )
//to pass the VALUE of the variable into the column formula, rather than passing the variable itself
//In this case, it wouldn't matter, since I delete the formula immediately after creation, but it's good practice
Eval(
	Eval Expr(
		dt << New Column( "Batch", character, Formula( Expr( prefix ) || Right( Char( :Batch Number ), rootlength, "0" ) || Expr( suffix ) ) )
	)
);
dt:Batch << Delete Formula;

[#6 · Accepted solution]

This won't create batch numbers but just the numbers.

Names Default To Here(1);

dt = Data Table("Format");

Summarize(dt, uniq = By(:Batch));

first_val = Num(Substr(uniq[1], 4, Length(uniq[1]) - 5));
last_val = Num(Substr(uniq[N Items(uniq)], 4, Length(uniq[1]) - 5));

dt = New Table("Res",
	New Column("R", Numeric, Ordinal, Values(first_val::last_val))
);

You can add batch numbers back by transforming that item list to string and create table from that (same first_val and last_val as in previous)

batches = Transform Each({item}, As List((first_val::last_val)`),
	"245" || Right("00000" || Char(item), 5) || "00"
);
dt = New Table("Res2",
	New Column("R", Character, Ordinal, Values(batches))
);

[#7]

Thanks Jarmo! This is perfect. 

While writing this post, I was bragging about how incredible the JMP community is, and I was not disappointed! 

Cheers, Christian 

[#8]

You can create rows by incrementing the batch numbers sequentially.