Home
Distribute by combine duplicates
This example shows how duplicate transactions can be combined before performing a pivot and filtering operation.

Load some data with lots of duplicates

Script
  1. AddData , TransactionsForActual.csv

This is data that comes from traditional transaction database. It has a lot of duplicates and it has not yet been converted to crosstab format. The first step is to aggregate records into summaries. In OLAP, the result of this aggregation is the lowest level in the hierarchy. This example shows how easy it is to convert transaction data into hierarchical crosstabs, just like OLAP, but without the limitations or cost.

Name Data Region Position Value
Ted Burton 01/01/2006 Central Account Manager 8,475
Nell Gordon 01/01/2006 Central Sr. Account Manager 21,800
Bonnie Mills 01/01/2006 Central Account Manager 6,450
Juan Perez 01/01/2006 Central Account Manager 6,250
Jonathan Ryan 01/01/2006 Central Account Representative 0
Marcus Saul 01/01/2006 Central Account Manager 6,250
Jerry Wong 01/01/2006 Central Sr. Account Manager 24,750
Will Hill 01/01/2006 Federal Account Representative 4,050
Gil Jacobs 01/01/2006 Federal Account Manager 14,400
Larry Kelley 01/01/2006 Federal Account Manager 300

 

Combine the duplicates

Script (continued)
  1. combineduplicates,Sum

 

The lowest detail transactions have been combined. In this case, we chose to sum the records, but easily could have computed averages or medians.

Name Data Region Position Count Value
Scott Williams 01/12/2006 West Account Manager 4 106,100
Emma Thatcher 01/12/2006 West Account Manager 4 68,300
Patrick Roberts 01/12/2006 West Account Manager 4 96,100
Andy Patterson 01/12/2006 West Account Manager 4 152,700
Mike Li 01/12/2006 West Sr. Account Manager 4 14,300
Kathy Holmes 01/12/2006 West Account Manager 4 198,900
Jake Grey 01/12/2006 West Account Representative 4 29,100
Marcia Brady 01/12/2006 West Account Manager 4 165,900
Susan Ball 01/12/2006 West Sr. Account Manager 4 276,600
John Smith 01/12/2006 Southeast Account Manager 4 221,800

 

Convert the table to a crosstab (pivot)


Script (continued)
  1. select,column,Last
  2. swaptextcolumnwithcolumnbyindex,0
  3. unselect, column , last

This converted the aggregated data into a crosstab. This command gives MySQL users access to the same capability as Microsoft PivotTable and the SQL Server SQL Pivot command, except with better more powerful results. The result is a crosstab, but (to continue the example) we realized there were too many data columns for our purposes, and they aren't in the order we wanted. We will now show how easy it is to show only the trailing few data columns, and for them to display in exactly the order we want.

Name Region Position 12/06 11/06 10/06 09/06 08/06 07/06 06/06 05/06 04/06 03/06 02/06 01/06
Scott Williams West Account Manager 106,100 84,000 56,900 48,800 130,800 9,900 77,000 24,400 200 163,800 71,100 1,000
Emma Thatcher West Account Manager 68,300 1,600 23,800 35,200 90,700 83,300 203,600 75,800 31,100 175,900 600 46,200
Patrick Roberts West Account Manager 96,100 27,500 39,600 71,200 45,800 49,300 131,900 88,200 55,900 173,400 46,000 45,900
Andy Patterson West Account Manager 152,700 103,800 31,400 75,800 82,500 79,100 7,500 34,200 43,000 3,600 10,600 9,000
Mike Li West Sr. Account Manager 14,300 163,300 100,800 230,500 211,800 5,800 321,600 199,000 39,200 314,600 190,800 4,500
Kathy Holmes West Account Manager 198,900 2,000 9,800 86,700 56,700 65,500 195,700 122,700 18,700 34,700 43,500 5,600
Jake Grey West Account Representative 29,100 13,000 42,300 114,100 1,200 700 49,700 32,400 5,000 72,700 8,400 10,400
Marcia Brady West Account Manager 165,900 91,500 44,100 93,000 131,400 41,100 65,100 46,900 12,900 81,800 16,700 38,700
Susan Ball West Sr. Account Manager 276,600 220,000 127,500 270,800 25,600 57,000 192,900 148,400 13,200 39,400 119,300 93,400
John Smith Southeast Account Manager 221,800 18,400 89,900 32,400 25,600 91,700 185,500 42,700 9,900 64,700 102,700 18,300

 

Keep only six specific months of data

Script (continued)
  1. SortByLabel , column , true , true , 01/06/2006~01/07/2006~01/08/2006~01/09/2006~01/10/2006~01/11/2006~01/12/2006
  2. changelabelcaption,False,Column,/01/,/temp/
  3. changelabelcaption,False,Column,01/,
  4. changelabelcaption,False,Column,temp/,01/
  5. changelabelcaption,False,Column,2006,06

We reduced it to the trailing six months of data, and they appear in a customized order, specific to this report. Then, to take the example further, we converted the column names from their database provided format. This allows us to make this page to be more user friendly and the labels are consistent with other data that may have been loaded from another data source (such as a spreadsheet). If we wanted to, it would now be easy to compare these columns of data from another source, and the columns would match. 

Name Region Position 06/06 07/06 08/06 09/06 10/06 11/06 12/06
Scott Williams West Account Manager 77,000 9,900 130,800 48,800 56,900 84,000 106,100
Emma Thatcher West Account Manager 203,600 83,300 90,700 35,200 23,800 1,600 68,300
Patrick Roberts West Account Manager 131,900 49,300 45,800 71,200 39,600 27,500 96,100
Andy Patterson West Account Manager 7,500 79,100 82,500 75,800 31,400 103,800 152,700
Mike Li West Sr. Account Manager 321,600 5,800 211,800 230,500 100,800 163,300 14,300
Kathy Holmes West Account Manager 195,700 65,500 56,700 86,700 9,800 2,000 198,900
Jake Grey West Account Representative 49,700 700 1,200 114,100 42,300 13,000 29,100
Marcia Brady West Account Manager 65,100 41,100 131,400 93,000 44,100 91,500 165,900
Susan Ball West Sr. Account Manager 192,900 57,000 25,600 270,800 127,500 220,000 276,600
John Smith Southeast Account Manager 185,500 91,700 25,600 32,400 89,900 18,400 221,800

 

 
< Prev   Next >