Home
Create a crosstab

Although data can be obtained from many sources, it's rarely in the format that you need. With nextanalytics, it's easy to get what you want. Easy, dynamic generation of crosstabs from a wide variety of data sources is usually the first step toward understanding your data, especially if you're trying to develop good charting and visualization. 

Provide an Input File or SQL Command

Nextanalytics can read CSV files, and it can run SQL queries for you.  It helps if you can modify the CSV file or the SQL Statements by inserting prefixes text_, rowlabel_, and a numeric_ at the right place. This give hints to nextanalytics about how to categorize the columns of data.  Ideally, you would modify your CSV file to be like this example:

If you were obtaining data from SQL, this is what you would do as a SQL command:

Select firstname as rowlabel_firstname, address as text_address, age as numeric_age  from   customers

Notice that text_, rowlabel_, and a numeric_ were inserted. This is all you would need to do to make loading that data a one line command in nextanalytics.

If you are unable to alter your inputs, don't worry! nextanalytics allows you to specify those values through other means. Sometimes this might be necessary because the incoming data file is dynamically generated by another application or is too large to open with a text editor.

Script Commands - AddData, SwapTextColumnWithColumn

Overview: AddData loads a data file, SwapTextColumnWithColumn performs a Pivot operation Let's examine the script lines, and what the effect of them running are, one by one.

adddata , transactions.csv

A Sample Transaction File
This sample data set will be used to demonstrate how easy it is to convert a tabular data set into a crosstab. In the following page, all unique members of the Date column are made into data columns. This is very similar to the process of creating a PivotTable in Excel or the SQL Server Pivot command.
Name Date Region Position Value
Ted Burton 01/06 Central Account Manager 33,900
Nell Gordon 01/06 Central Sr. Account Manager 87,200
Bonnie Mills 01/06 Central Account Manager 25,800
Juan Perez 01/06 Central Account Manager 25,000
Jonathan Ryan 01/06 Central Account Representative 0
Marcus Saul 01/06 Central Account Manager 25,000
Jerry Wong 01/06 Central Sr. Account Manager 99,000
Will Hill 01/06 Federal Account Representative 16,200
Gil Jacobs 01/06 Federal Account Manager 57,600
Larry Kelley 01/06 Federal Account Manager 1,200


swaptextcolumnwithcolumn , Date

Crosstab Version of the Transactions
The example has created numeric columns and then matched the transaction record values under the appropriate column.

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
Please note: The user interface is open source so you can customize it to display the data exactly the way you require.  You can even use it to export to various file formats or to create web services to suit your specific application needs.
 
< Prev   Next >