4.6.2 Creating Analysis Templates using Set Column Value

AnalysisTemplate-SetColVal

Summary

This tutorial will demonstrate how to add a column, set up Before Formula Script and have that script run whenever data changes in other columns. This technique can be used to create an Analysis Template for repeated analysis of similar data.

Minimum Origin Version Required: Origin 8.0 SR6

What you will learn

• How to use Set Column Values to create an analysis template
• How to select rows via the Go to function

Steps

1. Import the data from \Samples\Statistics\automobile.dat into a newly created workbook, as below. In this example, we will extract data, according to the Make column, into different worksheets.

   

2. Add an empty column to the worksheet and bring up the Set Column Values dialog of the column. In the Before Formula Script box, enter the script below.

   // Data range on which to perform discrete frequency count
   range makeCol = !col(make);
   // Worksheet to be extracted
   range sourceWks = !;
   
   // Clear worksheets
   int sheetNum = page.nlayers;
   int colNum = wks.ncols - 1;
   if (sheetNum>1)
   {
   	for (jj=2; jj<=sheetNum; jj++)
   	{
   		layer -d 2;
   	}
   }
   
   // Tree variable to hold discfreqs outputs
   tree tr;
   // Perform discrete frequency count
   discfreqs irng:=makeCol rd:=tr;
   // String array to get result from tree
   StringArray sa;
   sa.append(tr.FreqCount1.Data1);
   
   if( sa.GetSize() != NANUM )
   {
   	// Loop to extract data
   	for (ii=1; ii<=sa.GetSize(); ii++)
   	{
   		string sn$ = sa.GetAt(ii)$;
   		// Extract condition string
   		string cond$ = "makeCol$ = " + sn$;
   		// Create worksheet with different Make name
   		newsheet name:=sn$ cols:=colNum outname:=on$ active:=0;
   		// Extract data
   		wxt test:=cond$ iw:=sourceWks c2:=colNum ow:=on$;
   	}
   }

   This script will first perform a discrete frequency count on the Make column to get distinct values for Make. It will then create a new worksheet for each brand and extract data into these sheets.

   

3. Make sure the recalculate mode is set to Auto and click OK. The data will be separated into different worksheets. Then the empty column (H) with a green lock icon indicates that this procedure can be updated automatically.

   

4. There are 18 makes of cars in the source data, so 18 new worksheets were created. Now we can check whether auto-update works.
   Go to the Honda tab. Notice that there are 19 rows.
5. Switch to the first sheet. Scroll down and delete the last row.

   

   Then auto-update will be triggered. Go to the Honda tab. You will see there are only 18 rows.