Creating Temporary Data Table (?) to Replicate t=0 Data for Graph Builder

FAS

I am trying to use Graph Builder to visualize stability data. I have run into two issues with the existing data table and Graph Builder script/report that I would appreciate any suggestions to address!

 

In this first case, I need to create a temporary data table that reassigns the stability condition "Ambient" as "0 month" data at each of the other stability conditions (25/60, 30/65 and 40/75). In the report below, you can see that Ambient is in its own category which is an awkward way of thinking about initial testing results. I do not want to change the actual data table that a user would input data into; I am only trying to graphically replicate the t=0 Ambient data for each condition (25/60, 30/65 and 40/75). 

 

 

In the second case, I am just trying to size the "Ambient" category so that there isn't so much dead space before the next category (25/60) begins. In this data set, "Ambient" can only occur at t=0, so there is no reason for the other months (x-axis) to appear.

 

Thanks in advance!

 

 

 

txnelson

I have the solution for your first question, but it is too late at night for me to work on the second question.  If possible, I will work on it tomorrow.

But here is the first questions solution, to replicate all of the Ambient 0 data to all of the other Storage conditions.

Interactively, here is what you would do:

1. Select all of the Ambient rows in the data table
2. Create a new data table using    Tables=>Subset   that contains only the Ambient data
3. Delete the Stability Condition column....it is no longer needed as will be seen below
4. Using the original data table, run the Summary Platform    Tables=>Summary
   1. Select the Stability Condition column as the Grouping column
   2. Click OK
5. Delete the N Rows column from the new data table....it is not needed
6. Delete the row that has Ambient as the Stability Condition value.
7. Now, by using a Cartesian Join, we can create all possible combinations of the rows in the subsetted Ambien data table with the rows in the summary data table. To do this, go to the summarized data table and select     Tables=>Join.
   1.  Select the Ambient data table as the data table to join with the summarized table
   2. Click on the "By Matching Columns" pull down box and select "Cartesian Join"
   3. Click on OK
8. Now we next have to create a subset of the original data table that has all of the non ambient rows.
   1. Go to one of the Ambient rows and right click on the cell that has the Ambient value and right click and select "Select Matching Cells".
   2. We now want to Invert the row selection, so go to the row state column(the column that has the row numbers) and right click on one of the selected rows, and select " Invert Selection"
   3. Using the     Tables=>Subset  pull down menu, create a subset that has all columns, but only the selected rows.
   4. The last step is to put together this new subset, with the data table that was generated by the Cartesian Join.
      1. Go to     Tables=>Concatenate
      2. Select as the data table of be concatenated, the Cartesian Join data table
      3. Check the "Append to first table" check box
      4. Click on OK
   5. Now you can run the Graph Builder Platform and create the graph you want
   6. Below is a script that creates an Example data table, and then using JSL walks through the steps as defined above, and creates the new subset data table

   7. 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 );