Scripts for Growing Data Tables

[#1]

Jim (aka "superuser extraordinaire") was kind enough to supply the following script to help transform

to 

using

1. 1. names default to here( 1 );
      
      /*****************************************************************************/
      // Create a sample data table and original graphs
      /*****************************************************************************/
      dt = new table(
      	"Example",
      	New Column( "Months of Storage" ),
      	New Column( "Stability Condition", character ),
      	New Column( "Lot", character ),
      	New Column( "API001 %LC",format("Percent",4,0) )
      );
      MoList = {0, 3, 6, 9, 12, 18};
      StabList = {"Ambient", "25/60", "30/65", "40/75"};
      LotList = {"001", "002", "003", "004", "005", "006"};
      
      For( m = 1, m <= N Items( MoList ), m++,
      	For( s = 1, s <= N Items( StabList ), s++,
      		For( l = 1, l <= N Items( LotList ), l++,
      			For( i = 1, i <= 16, i++,
      				dt << add rows( 1 );
      				:Months of Storage[N Rows( dt )] = MoList[m];
      				:Stability Condition[N Rows( dt )] = StabList[s];
      				:Lot[N Rows( dt )] = LotList[l];
      				:Name( "API001 %LC" )[N Row( dt )] = Random Integer( 65, 110 ) / 100;
      			)
      		)
      	)
      );
      
      dt << select where( :Months of Storage == 0 & :Stability Condition != "Ambient");
      dt << delete rows;
      dt << select where( : Stability Condition == "Ambient" & :Months of Storage !=0);
      dt << delete rows;
      
      dt:stability condition << set property("value order",{Custom Order( {"Ambient", "25/60", "30/65", "40/75"} )});
      
      dt << Graph Builder(
      	Size( 570, 488 ),
      	Show Control Panel( 0 ),
      	Variables(
      		X( :Lot ),
      		Y( :API001 %LC ),
      		Group X( :Months of Storage ),
      		Group Y( :Stability Condition )
      	),
      	Elements( Points( X, Y, Legend( 7 ), Jitter( "None" ) ) )
      );
      dt << Graph Builder(
      	Size( 570, 492 ),
      	Show Control Panel( 0 ),
      	Variables(
      		X( :Months of Storage ),
      		Y( :API001 %LC ),
      		Group X( :Stability Condition ),
      		Group Y( :Lot )
      	),
      	Elements( Points( X, Y, Legend( 7 ) ) )
      );
      
      // End of initial data table creation and original chart creation
      
      /*****************************************************************************/
      // Replicate the Ambient Month 0  values to all levels of Stability Condition 
      /*****************************************************************************/
      
      // Create a new data table of just the Anbient data
      dt << select where(:Stability Condition == "Ambient");
      dtAmb = dt << subset( selected rows(1), selected columns(0));
      // Delete the Stability Condition column 
      dtAmb << delete columns(:Stability Condition);
      
      // Use the Summary Platform to create a table with just the unique Stability Condition values
      dtSum = dt << Summary(
      	Group( :Stability Condition ),
      	Freq( "None" ),
      	Weight( "None" )
      );
      // Delete the N Rows Column;
      dtSum << delete columns(:N Rows);
      // Delete the Ambient row
      dtSum << select where(:Stability Condition == "Ambient");
      dtSum << delete rows;
      
      // Change all of the 
      
      // Cartesian Join of the tables to generate all combinations of the two tables
      dtJoin = dtSum <<
      Join( With( dtAmb ), Cartesian Join );
      
      // Now create a subset data table from the original data table with all non ambient rows
      dt << select where( :Stability Condition != "Ambient");
      dtNoAmbient = dt << subset(selected rows(1), selected columns(0));
      
      // Concatenate the non ambient data with the data from the cartiesian data
      dtNoAmbient << concatenate(dtJoin, append to first table);
      
      // Create the Graph Builder map with the new data table
      dtNoAmbient << Graph Builder(
      	Size( 570, 488 ),
      	Show Control Panel( 0 ),
      	Variables(
      		X( :Lot ),
      		Y( :API001 %LC ),
      		Group X( :Months of Storage ),
      		Group Y( :Stability Condition )
      	),
      	Elements( Points( X, Y, Legend( 7 ), Jitter( "None" ) ) )
      );
      
      // Clean up the no longer needed data tables
      close( dtAmb, nosave );
      close( dtSum, nosave );
      close( dtJoin, nosave );

You can see that I was trying to reproduce the "Ambient" data as t=0 for each of the other conditions (25/60, 30/65 and 40/75). To that end, the script works brilliantly. However, it doesn't accept new data entered into the data table (for example more lots) and is not flexible with respect to "Months of Storage" shown. The script is using a predefined list to display "Months of Storage" but this might be different or change in the future as new data is entered.

How can I make the script more flexible so that all data in the data table is evaluated in the future when new data becomes available?

Thanks in advance!

[#2]

Can you break down your problem to the exact point, where you need help?

Sounds like you have to look in your updated table for the new list with added items, instead of having a defined list by script. Would you need that code snippet?

[#3]

Hello George,

Yes, that is correct. Users are going to update the data table whenever new data is available. New data may have entries for Months of Storage or Storage Condition or Lot that have not been used before. 

For example, we may anticipate test results for 0, 1, 3, 6 months, etc. but may find that additional testing between testing intervals is necessary (i.e., 4 months). So we need the list that is storing values for Months of Storage to use whatever is entered into that column. We can't anticipate every entry and we do not want to inadvertently exclude data in the report. The same is true for Storage Condition or Lot.

The Storage Condition "Ambient" and Months of Storage "0" is a special case. Here we are trying to replicate the "Ambient" results as each Storage Condition that is NOT Ambient and assign the test results to "0" Months of Storage.

[#4]

Hi, this may work for you:

• It assumes that the original data table--the first one created--from the previous script is current, with derivative reports and graphs closed (to allow sorting in-place)
• The "Ambient" category remains present, but isn't shown.
• When additional data is available, just re-run the script.

Cheers,

Brady

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

//remove "old" appended rows, if any
dt << select where( :stability condition != "Ambient" & :Months of storage == 0 );
Try( dt << delete rows );

//sort table to ensure "Ambient" block (all time == 0 in beginning)... graphs/reports must be closed
dt << sort( by(:Months of storage, :stability condition, :lot), order(ascending, ascending, ascending), replacetable);
	
//append this data and rename, once for each condition
nr = N Row( dt << get rows where( :stability condition == "Ambient" ) );
For( i = 1, i <= 3, i++,
	cur = N Row( dt );
	dt << addrows( nr );
	dt[(cur + 1) :: (cur + nr), 1 :: N Col( dt )] = dt[1 :: nr, 1 :: N Col( dt )];
	dt:stability condition[(cur + 1) :: (cur + nr)] = Words( "25/60,30/65,40/75", "," )[i];
);

//graph... the ambient data is present but we don't have to show it
Graph Builder(
	Size( 570, 484 ),
	Show Control Panel( 0 ),
	Variables(
		X( :Lot ),
		Y( :API001 %LC ),
		Group X( :Months of Storage ),
		Group Y( :Stability Condition, N View Levels( 3 ), First View Level( 2 ) )
	),
	Elements( Points( X, Y, Legend( 7 ) ) )
);
 

[#5]

Thanks, Brady! I will give this a try today.