Subscribe Bookmark RSS Feed

Formula within groups of values

aallman

Occasional Contributor

Joined:

Feb 28, 2017

Hi

 

I have a table of process steps and process times for different IDs. However, some of my process steps occur more than once in the process for each ID. I would like to create a column that separates all the process steps into either "synthesis" or "processing". These groups would be determined based on if they occur before or after another process step.

 

I want the formula to find the time of the spawn step FOR EACH ID and compare it to all the other process step times for that ID. 

I was trying to use some sort of loop, but I am new at scripting and loops are very confusing!

 

Is there any way to do this???

 

For example, what I have looks like this and I want to add the Category column.

 

 IDProcess StepProcess timeCategory
10Drying9:00Synthesis
10Spawned9:30Synthesis
10Drying10:00Processing
10Finished11:00Processing
11Drying2:00

Synthesis

11Spawned2:30Synthesis
11Drying2:45Processing
11Finished4:00Processing
12Drying7:30Synthesis
12Spawned7:45Synthesis
12Drying8:00Processing
12Finished9:00Processing

 

 

Any ideas??

Thank you!

1 ACCEPTED SOLUTION

Accepted Solutions
txnelson

Super User

Joined:

Jun 22, 2012

Solution

This script seems to be what you want.  It could be turned into a formula if you would prefer that

Names Default To Here( 1 );

dt = New Table( "Untitled 13",
	Add Rows( 12 ),
	New Column( "ID",
		Character,
		"Nominal",
		Set Values( {"10", "10", "10", "10", "11", "11", "11", "11", "12", "12", "12", "12"} )
	),
	New Column( "Process Step",
		Character( 16 ),
		"Nominal",
		Set Values(
			{"Drying", "Spawned", "Drying", "Finished", "Drying", "Spawned", "Drying", "Finished", "Drying", "Spawned", "Drying",
			"Finished"}
		)
	),
	New Column( "Process Time",
		Numeric,
		"Nominal",
		Format( "h:m", 12 ),
		Input Format( "h:m" ),
		Set Values( [32400, 34200, 36000, 39600, 7200, 9000, 9900, 14400, 27000, 27900, 28800, 32400] )
	)
);

dt << New Column( "Category", Character );

For( i = 1, i <= N Rows( dt ), i++,
	currID = dt:id[i];
	If( i == 1,
		spawnTime = dt:Process Time[(dt << get rows where( dt:id == currID & dt:Process Step == "Spawned" ))[1]]
	);
	If( i > 1,
		If( dt:ID[i] != dt:ID[i - 1],
			spawnTime = dt:Process Time[(dt << get rows where( dt:id == currID & dt:Process Step == "Spawned" ))[1]]
		)
	);
	If( dt:Process Time[i] <= spawnTime,
		dt:Category[i] = "Synthesis",
		dt:Category[i] = "Processing";
	);
);

 The script assumes the data are sorted by ID.  However, if the spawnTime was freshly calculated for each loop through the data, the table would not have to be sorted.  It would slow the script down, calculating the SpawnTime on each loop, but that might be a tradeoff you want to make.

Jim
1 REPLY
txnelson

Super User

Joined:

Jun 22, 2012

Solution

This script seems to be what you want.  It could be turned into a formula if you would prefer that

Names Default To Here( 1 );

dt = New Table( "Untitled 13",
	Add Rows( 12 ),
	New Column( "ID",
		Character,
		"Nominal",
		Set Values( {"10", "10", "10", "10", "11", "11", "11", "11", "12", "12", "12", "12"} )
	),
	New Column( "Process Step",
		Character( 16 ),
		"Nominal",
		Set Values(
			{"Drying", "Spawned", "Drying", "Finished", "Drying", "Spawned", "Drying", "Finished", "Drying", "Spawned", "Drying",
			"Finished"}
		)
	),
	New Column( "Process Time",
		Numeric,
		"Nominal",
		Format( "h:m", 12 ),
		Input Format( "h:m" ),
		Set Values( [32400, 34200, 36000, 39600, 7200, 9000, 9900, 14400, 27000, 27900, 28800, 32400] )
	)
);

dt << New Column( "Category", Character );

For( i = 1, i <= N Rows( dt ), i++,
	currID = dt:id[i];
	If( i == 1,
		spawnTime = dt:Process Time[(dt << get rows where( dt:id == currID & dt:Process Step == "Spawned" ))[1]]
	);
	If( i > 1,
		If( dt:ID[i] != dt:ID[i - 1],
			spawnTime = dt:Process Time[(dt << get rows where( dt:id == currID & dt:Process Step == "Spawned" ))[1]]
		)
	);
	If( dt:Process Time[i] <= spawnTime,
		dt:Category[i] = "Synthesis",
		dt:Category[i] = "Processing";
	);
);

 The script assumes the data are sorted by ID.  However, if the spawnTime was freshly calculated for each loop through the data, the table would not have to be sorted.  It would slow the script down, calculating the SpawnTime on each loop, but that might be a tradeoff you want to make.

Jim