Need an account? Click to Register
Home arrow Learn arrow Tutorials arrow Filter using text
Filter using text

Sometimes you have to search and select rows based on a text attribute within the row. nextanalytics often uses this before or after an analytic command.

Script Overview 

The script in the following section was recorded while using Author mode as explained in the first tutorial section. The following notes describe what each important command was doing. 

  • The AddData command loads a CSV file or a nextanalytics optimized data file. As an alternative, you can also load data from a SQL query.
  • ChangeAlertTo controls the coloring. The various parameters control what happens. In this example, the parameters are instructing the command to compare all the cells to the current column's average. This command can be experimented with using the Author Mode user interface.
  • SelectByCaptions is used to look for matching text strings in the designated column.  This command can be experimented with using the Author Mode user interface.
In the following script, PageCaption and PageDescription commands provide "business context" for each page being displayed.
Script
  1. adddata , actuals.csv
  2. PageCaption , "This is sample sales data"
  3. PageDescription , "This will be used to demonstrate how to remove or keep rows that have text values in certain columns.  It has been color coded to show when a cell value is above or below the column average."
  4. ChangeAlertTo,axis,-1,Column,Average,0,True,,-1
  5. ;--------------
  6. selectbycaptions,KeepBySel,Row,1,False,-1,False,,True,ExactMatch,1,Account Manager
  7. PageCaption , "Filtered to Include only Account Managers"
  8. PageDescription , "Being able to filter data using text is often the first step towards segmenting a set of data to a geographical or organizational area.  Notice how the color value changes because now only Account Managers are being included in the calculation of the column average."

Results of the Script

This is sample sales data
This will be used to demonstrate how to remove or keep rows that have text values in certain columns. It has been color coded to show when a cell value is above or below the column average.
Name Region Position 01/06 02/06 03/06 04/06 05/06 06/06 07/06 08/06 09/06 10/06 11/06 12/06
Ted Burton Central Account Manager 33,900 62,100 106,200 37,100 115,600 151,700 58,500 102,400 177,700 83,000 133,600 210,400
Nell Gordon Central Sr. Account Manager 87,200 172,700 300,100 85,700 49,400 47,500 68,700 11,400 123,200 109,200 202,100 152,400
Bonnie Mills Central Account Manager 25,800 75,100 77,300 71,500 32,900 14,700 44,000 13,400 227,000 98,400 120,000 173,800
Juan Perez Central Account Manager 25,000 32,900 117,600 67,900 104,600 62,700 55,600 54,800 18,000 18,500 109,600 233,000
Jonathan Ryan Central Account Representative 0 0 0 0 0 0 16,600 32,000 34,800 6,700 4,800 84,800
Marcus Saul Central Account Manager 25,000 46,000 22,500 25,800 76,400 199,200 11,100 67,800 203,300 44,100 65,300 193,500
Jerry Wong Central Sr. Account Manager 99,000 159,700 63,200 7,200 169,700 286,200 83,700 231,600 251,700 155,600 208,000 314,900
Will Hill Federal Account Representative 16,200 15,700 22,300 13,500 43,100 29,900 11,900 46,600 44,200 32,000 6,100 122,600
Gil Jacobs Federal Account Manager 57,600 42,500 88,200 23,600 74,700 186,400 66,400 113,700 214,900 18,300 142,700 198,700
Larry Kelley Federal Account Manager 1,200 22,900 97,400 68,700 99,200 38,100 86,900 107,600 31,700 63,900 109,500 203,800
Filtered to Include only Account Managers
Being able to filter data using text is often the first step towards segmenting a set of data to a geographical or organizational area. Notice how the color value changes because now only Account Managers are being included in the calculation of the column average.
Name Region Position 01/06 02/06 03/06 04/06 05/06 06/06 07/06 08/06 09/06 10/06 11/06 12/06
Ted Burton Central Account Manager 33,900 62,100 106,200 37,100 115,600 151,700 58,500 102,400 177,700 83,000 133,600 210,400
Bonnie Mills Central Account Manager 25,800 75,100 77,300 71,500 32,900 14,700 44,000 13,400 227,000 98,400 120,000 173,800
Juan Perez Central Account Manager 25,000 32,900 117,600 67,900 104,600 62,700 55,600 54,800 18,000 18,500 109,600 233,000
Marcus Saul Central Account Manager 25,000 46,000 22,500 25,800 76,400 199,200 11,100 67,800 203,300 44,100 65,300 193,500
Gil Jacobs Federal Account Manager 57,600 42,500 88,200 23,600 74,700 186,400 66,400 113,700 214,900 18,300 142,700 198,700
Larry Kelley Federal Account Manager 1,200 22,900 97,400 68,700 99,200 38,100 86,900 107,600 31,700 63,900 109,500 203,800
Phillip White Federal Account Manager 62,300 106,000 82,200 18,000 41,900 74,100 71,100 94,600 125,300 56,900 19,600 182,900
Anne Grenier International Account Manager 70,200 113,200 14,000 58,100 87,600 202,200 80,600 85,400 137,700 100,000 27,200 201,900
Bill Wright International Account Manager 19,100 92,700 140,500 84,700 108,500 166,500 18,400 4,400 77,600 88,200 129,100 76,700
Mike Chang Northeast Account Manager 39,500 85,900 15,300 18,000 101,500 32,900 47,200 135,000 175,400 86,600 21,800 228,000
 
< Prev   Next >