A Complete Guide to Pivot Tables

r3lody (Raymond Lodato) writes "Like most people, I've only scratched the surface (well, maybe I gouged it a bit) of the capabilities of the Microsoft Office products. There are more features buried in them than most users ever discover. I use Microsoft Excel frequently in my job to analyze all sorts of data. When they came out with Pivot Tables, I dabbled in them and found several uses for them. However, documentation being what it is, I never really got to understand and utilize Pivot Tables' full capabilities. Now, Apress has published A Complete Guide to Pivot Tables: A Visual Approach, by Paul Cornell." Read on for the rest of Lodato's review. A Complete Guide to Pivot Tables: A Visual Approach author Paul Cornell pages 368 publisher Apress rating 10/10 reviewer Raymond Lodato (rlodato AT yahoo DOT com) ISBN 1590594320 summary A well-researched step-by-step tutorial on the use and programming of Microsoft Excel PivotTables for data analysis.

If you have any need to analyze data in Excel, you must read this book. I learned so much more about PivotTables after I read it that they are now actually useful, rather than just being something I would occasionally try out. Every chapter is packed with excellent information in an easy-to-follow format. A beginning to intermediate user can understand most of the book; only the chapter on programming PivotTables requires intermediate to advanced knowledge to fully comprehend.

Cornell takes a tutorial approach to explaining what PivotTables are, what they are capable of doing, and how you can apply them to your needs. Each chapter in the Complete Guide gives you an overview of a single topic, a series of step-by-step examples, a Try-It section for more practice, and a summary of next steps. The book was written for Excel 2003, but most of the techniques can be applied to Excel 2002 and even Excel 2000.

When you read this book, I would recommend that you sit at your computer to try these techniques as you read them. I tried to just read the book at first, but you really get itchy to try each feature out. Take it in sequence, as there is a definite building from one chapter to the next.

Chapter 1 gives you an overview of the PivotTable feature, what it's meant to do, and why you would use it. Chapter 2 starts the in-depth training of building basic PivotTables from Excel Lists, external data sources, other PivotTables, etc. It also includes tips on formatting the information and tweaking the fields and table to your liking. Chapter 3 goes even deeper, with information on advanced settings, filters, calculated fields, and other little gems that make analysis easier. These three chapters complete your basic training and lead to chapter 4, "Using PivotTables in the Real World." Paul proceeds to give not just one, but three examples of how PivotTables could be used to provide insight into company operations.

While everything I'd known about PivotTables before picking up this book was covered in the first three chapters, the last three chapters explain additional capabilities that really make Excel valuable for data analysis. Chapter 5 explains PivotCharts, which are simply a graphical representation of the information shown in a PivotTable. Paul goes into detail on the different chart types and how they interact with the underlying PivotTable.

If you need to distill and analyze multidimensional, relational data, PivotTables are up to the task, as chapter 6 will demonstrate. The book describes how you use cube files, OLAP databases, and Microsoft Query to get the data and manipulate it. If you're a really capable programmer, comfortable with VBA, chapter 7 shows you how to work with PivotTable programmatically. There is program after program showing you exactly how to get at the data, massage it, and create the resulting PivotTable.

Finally, there is one appendix that describes the differences between Excel 2000, Excel 2002, and Excel 2003. I was originally reviewing this book while using a computer with Excel 2000. I was delighted to find out that most of the information in the book works exactly as Paul described (although the screen shots didn't match). I did recheck some of the examples on my other machine running Excel 2002, and had no problems at all.

Granted, this book is specifically written for Microsoft Excel. However, OpenOffice, the free competitor from Sun MicroSystems, mimics most of the Microsoft Office suite. How does it compare, you ask? Well, OpenOffice has a similar facility to PivotTables, called DataPilot; however, DataPilot is primitive in comparison. For example, you must select the data to summarize, choose (from the menu bar) Data --> DataPilot --> Start, then drag the fields to the appropriate place in the diagram and click OK. Like Excel, you can freely move the fields between row, column, and data areas, and change the data operation from Sum to Min, Max, or a number of others. Unlike Excel, there isn't much more you can do. You don't have Page fields; you can't sort fields on their data; PivotCharts aren't represented; and there's no programming. If you only want to do simple data analysis in OpenOffice, you can get the basics from chapters 1 and 2 of this book.

You can purchase A Complete Guide to PivotTables: A Visual Approach from bn.com. Slashdot welcomes readers' book reviews -- to see your own review here, read the book review guidelines, then visit the submission page.

Comments

What IS a pivot table anyway?

[Hanzie]

I'd really like to know what the hell a pivot table is. From reading the review at Amazon and the one here a 'pivot table' is:

'something really useful for analyzing data, and really great'

Sorry, sahib, but if you want me to read (let alone purchase) a book about pivot tables, I'm going to need some explanation of what they are first.

I don't pretend that I'm speaking for any number of people here. Perhaps every slashdotter besides me knows what a pivot table is, but the description of the review would seem to imply otherwise.

Could anybody else describe a pivot table?
***********
Heh, heh. Google's first hit for "pivot table tutorial" is already slashdotted.

Re:What IS a pivot table anyway?

[FrereTuck]

Read this: http://www.cpearson.com/excel/pivots.htm

Re:What IS a pivot table anyway?

[KernelHappy]

Think of it like a movie, if the article told us what a pivot table was, we wouldn't need to buy the book, then book sales would plummet and then the publishing industry would start building anti-concept copying mechanisms into text. I think it's all for the best it remains a mystery.

Re:What IS a pivot table anyway?

[Chundra]

I'm going to put MCPTE (Microsoft Certified Pivot Table Engineer) on my resume. Wheee!

Re:What IS a pivot table anyway?

[roman_mir]

Pivot table means that; it's pivotal. I mean, you're a -- you've been given pivotality, and you're viewed as a pivotal entity. And therefore the relationship between the data and tables is one between pivotal entities."

Re:What IS a pivot table anyway?

[nacturation]

Sorry, sahib, but if you want me to read (let alone purchase) a book about pivot tables, I'm going to need some explanation of what they are first.

Perhaps it could just be that if you don't know what it is, the book isn't trying to sell to you. For example, would you expect The Washington Manual Hematology and Oncology Subspecialty Consult to explain to you what hematology and oncology are?

Re:What IS a pivot table anyway?

[slamb]

I'd really like to know what the hell a pivot table is.

First, you've got to have some data to describe. Here's one I'm looking at right now: I've got a table in my Oracle database that lists services doctors performed on given dates, and how much they charged for them.

A conventional report would describe that data one-dimensionally. Total amount of money each doctor charged or total amount of money charged in each date range.

A pivot table (or matrix report, as Oracle Reports calls them) shows the same data two-dimensionally. Down the left is a list of doctors. Across the top is a list of months. Cells in the middle show how much each doctor billed in a given month. There are monthly totals at the bottom, doctor totals on the right, and a grand total in the lower right.

So essentially, a pivot table is just a tool for putting data from a RDBMS into the spreadsheet form you know and love.

Re:What IS a pivot table anyway?

[RandomWhiteMan]

A pivot table can be used to combine data in a similar set together. Say you have a lot of data points in a spreadsheet, say inventory levels for socks by the purchase order they come in on. You have the number of socks, it's PO number, it's color, and the date the shipment is received.

You can use a pivot table turn this data points (all data for that point is contained on a row, with the top row giving the header for each column quantity, color, PO, date,) into a table. This way you can have a table for the total number sock delivered each week by color. Or just the number of shipments of socks of a particular color in a month.

Might not sound really useful, but if you get a lot of data on a day to day basis out of some SQL query and need to reorganize it into a more readable format (or make some nice charts for the PHB,) it is a life saver. I've also found other quick uses for when you need all your data in a certain format and either lack the skill or time to program a custom SQL query to get the data out in the right format. Pivot Tables, Text-to-Columns, and the CONCATENATE function in excel have been life savers for me before on projects once I get to the number crunching point.

Re:What IS a pivot table anyway?

[RandomWhiteMan]

I would, at my place of work that will get you major bonus points when applying for a job.

Re:What IS a pivot table anyway?

[ackthpt]

I'd really like to know what the hell a pivot table is.

This bugged me a lot back when I used to get requests for them. I always assumed it was a new word Microsoft came up with for something else the world has known about for 30 or more years by a conventional name, like Translation Table. These seemed to be what users were requesting when I gave them CSV or Tab delimited files.

Re:What IS a pivot table anyway?

[prockcore]

I'd really like to know what the hell a pivot table is

Obviously it's a table with one leg shorter than the other. They're useful for annoying the rest of your family at dinner time.

Re:What IS a pivot table anyway?

[Sai Babu]

So there's a whole book dedicted to a friggin format change? Awk handles this with ease.

Re:What IS a pivot table anyway?

[myukew]

a pivot table is - like you may have expected - another feature of the office suite most people don't need, hell they even don't know what it's supposed to do.

like this little paperclip which always annoys you with it's "tips".

- It seems as if you're writing a /. comment. Do you need any help? -

Re:What IS a pivot table anyway?

[Ohreally_factor]

But I think one could expect to be given a clue if the review of The Washington Manual Hematology and Oncology Subspecialty Consult to explain it, especially if that review appeared in a general purpose tech site.

Besides which, hematology and oncology are not obscure terms, and it is quite easy to find a definition. Pivot Tables, otoh, are a specialized phrase to a specific program (or programs, I guess, given the mentions of the Lotus product above), and even the review itself points out that this is a "buried feature".

Saying, "If you have to ask, you don't need to know," in this situation is a bit rude.

Re:What IS a pivot table anyway?

[dynamo]

> Heh, heh. Google's first hit for "pivot table tutorial" is already slashdotted.

That rocks, everyone. I'm proud of you.

Re:What IS a pivot table anyway?

[man_ls]

Text-to-Columns alone has saved a company I consulted for thousands of dollars.

They had a list of data they needed imported from one database to another...and I had to clean it up.

First thing I did was construct the delimiters in the way the new database wanted: using Text-to-Columns.

They had hired some high schoolers to do manual data entry at $6 an hour -- but they were only able to do maybe 20 or 30 records an hour at most. It would have taken them 200 hours or so to process the entire database that way. So, for the minimum fee of $200, I used Excel to reorganize their data, into a more automatically-importable format. Saved them $1000, made myself $200. :)

Re:What IS a pivot table anyway?

[Moofie]

If you think it's that simple, you don't get it.

Re:What IS a pivot table anyway?

[Moofie]

That paperclip would have been able to tell you that you don't need an apostrophe when you are using "it" as a possessive.

And for the people who DO know how to use pivot tables (I know only enough to be dangerous) they are huge time savers.

But hey...you don't use them, so probably nobody does.

Re:What IS a pivot table anyway?

[iabervon]

Seems to be like a SQL query with a "group by" clause, except that it's been make user friendly, so you need a whole book about it, and it also tries to deal with the fact that it's a spreadsheet and the data has been mangled accidentally by the user.

Re:What IS a pivot table anyway?

[Anonymous Coward]

Was that meant to sound like something George W. Bush would say? If he knew big words like data and table...

Re:What IS a pivot table anyway?

[Sai Babu]

Please enlighten me. I understand relational databases. 'push these buttons and out pops a report in this format is NOT an explanation'.

Re:What IS a pivot table anyway?

[kin_korn_karn]

From what I'm reading, and I may not understand correctly, a pivot table is something like the results of this SQL query:

select
category,
sum(values)
from categorized_values
group by category
order by category

The "pivoting" refers the ability to quickly change the grouped-by and ordered-by columns.

Now how wrong am I?

Re:What IS a pivot table anyway?

[roman_mir]

I am sure he has seen those new fancy tables they have nowadays, why just today he must have eaten from one.... Oh maybe he is even familiar with a more modern concept of diner plates

Re:What IS a pivot table anyway?

[Anonymous Coward]

http://www.democracynow.org/article.pl?sid=04/08/1 0/149259

Re:What IS a pivot table anyway?

[squidfood]

'push these buttons and out pops a report in this format is NOT an explanation'.

Okay, the format isn't magical, it's just a table. The magic-ish is the interface for changing it, basically, you can drag and drop ("pivot") your data categories (columns) and quickly say: okay, lets sum by widgets. Now by gadgets. Now by widgets that are gadgets.

All this can be done on the SQL command line. But this is one place where drag and drop works better than the command line (and I'm a sworn CLI addict for most things).

The un-magic about Excel pivot tables is that your're limited to 64k rows of data. In using these from Oracle, much of the trick is writing the base query to get a result down to 64K lines, then pivoting the results.

Re:What IS a pivot table anyway?

[Sai Babu]

OK, I did a little research. A pivot table is a fancy name for a feature in Excel that adjusts the calculation squares in your spreadsheet as you change the presentation format. This allows you to look at the data in your spreadsheet in a bunch of different formats without having to rearrange all the calculation squares yourself. I can see some aura of elegance there for the RDB and AWK impaired. You guessed it, I don't use excel.

Re:What IS a pivot table anyway?

[nacturation]

Saying, "If you have to ask, you don't need to know," in this situation is a bit rude.

I raised the possibility that this book wasn't intended for a general audience and no rudeness was intended. The fact that it's posted on the front page of Slashdot doesn't *necessarily* imply that it's a general topic -- though of course it could be. However, you are correct in saying that if it IS intended for everyone that an overview of what pivot tables are would be helpful.

Re:What IS a pivot table anyway?

[bob beta]

Some might argue (I wouldn't, but some would) that the "RDB and AWK impaired" are the people who know too much about those tools, and not enough about anything else.

I'm just sayin'. . .

Re:What IS a pivot table anyway?

[Sai Babu]

Thanks. I can see it's utility for quick and dirty and as you said limited size. While awaiting your reply I did a little google research and posted what I found elsewhere in this thread. Basically you rearrange the presentation and the magic rearranges the calculation squares so the right boxes show expected results. I'm new to GUI's so drag and drop is still a bit of a mystery ;-)

Re:What IS a pivot table anyway?

[whoever57]

I'd really like to know what the hell a pivot table is.

This is /. All you need to know about pivot tables is that they are a feature of a Microsoft tool.

Re:What IS a pivot table anyway?

[Angostura]

What IS Awk anyway?

You insensitive clod

Re:What IS a pivot table anyway?

[Sai Babu]

It's possible, but this RDB and AWK impaired person was able to answer the question that dozens of excel impaired couldn't or wouldn't. See my other posts on topic. I can see it's handy, but I still can't see a friggin book on the topic. Then again, there was this book on the shelf at Fry;s the other day. Something about Windows XP hacks. It was nothing more than a list of all the features in windows and the suggestion that you might want to modify them and tha the registry was the place to do it and that the tool was the registry editor. Nothing, ZIP, NADA on what you might want to change, why you might want to change it, what sections of the registry affected that feature. So yes, maybe a whole book. Is it mostly screen shots. I love screen shots. They let me compare my screen with the shot in the book so I can verify that I pushed the right buttons. I apologize but I feel like Homer Simpson sitting at his control panel when confronted by all those buttons. GUIitis? Ya think?

Re:What IS a pivot table anyway?

[Sai Babu]

AWK is tool that lets you write scripts for editing.

Plenty of tutorials about. I'm no expert so I use them.

Quoting from the intro to one. "Awk, named after its developers Aho, Weinberger, and Kernighan, is a programming language which permits easy manipulation of structured data and the generation of formatted reports. "

Be careful, you'll never be the same again.

Re:What IS a pivot table anyway?

[XenonDif]

Here's a shot at explaining... It's a way of breaking down multidimensional data and looking at in a few dimensions at at time. A pivot table will output a table with a maximum of 2-1/2 dimensions: x-axis, y-axis, and a filter. Nuance: Sometimes you have you have two sets of data that have the same dimensions, so the data itself can be a variable. For example, sales in $USD and in Qty would be two sets of data, but would share the same dimensions like salesman, date, region, customer, etc. Say you have an Oracle database with sales transactions from a resaurant. The possible dimensions that this data could have are pretty large. There would be more than this, but just of the top of my head you would have at least: name_of_waiter, date, time, day_of_week, hour, month, dish_name, dish_category, customer_name, form_of_payment, cashier_name With a pivot table, you could make a chart that had: "name_of_waiter" on the x-axis, "hour", filter to show the data from the month of May, (the 1/2 dimention), and looked at the data set $USD The nice thing about pivot tables in Excel is that you can generate these tables quickly with a few drag and drop commands and do neat things like have muliple dimensions in the same axis. You really have to play with it to see how neat it is.

Re:What IS a pivot table anyway?

[Ohreally_factor]

My apologies for implying you were rude! It's funny, but I have no qualms with calling some people the worst sorts of names, ensconced behind my slashdot identity*, yet I feel bad for even suggesting that your behavior was improper. I must have eaten something bad. =)

*I've been told I do this a bit too much in real life as well, so I can't just blame the internet.

Re:What IS a pivot table anyway?

[Anonymous Coward]

You could've used a Perl or Ruby script and hooked into the OLE stuff in Office to create an Excel table without having to open the program yourself. This is a great use of scripting languages, IMO, and is the sort of thing that vindicates their existance.

Re:What IS a pivot table anyway?

[nomadic]

And what exactly is a "tutorial"?

Re:What IS a pivot table anyway?

[Random_Goblin]

sayeth the sig "Am I the only slashdot user that thought that Minority Report qualified as a horror movie?

well if its the plot you you are refering to, I'd have to say it would be fair comment referring to it as horror, much of Philip K Dick's work is scary as hell. Probably not suprising considering he was a paranoid schizophrenic genius with a bad speed habit.

I don't know though are Orwell's "1984", or Bradbury's "Farenheit 451" Horror? what is a good definition of horror?

Re:What IS a pivot table anyway?

[ilikeitraw]

HAHAHAHAHA... That's the best thing I've read all day. Thank you :)

Re:What IS a pivot table anyway?

[Anonymous Coward]

And thus, of relatively little interest.

Re:What IS a pivot table anyway?

[rainman_bc]

Well, crap, I've been using them for years, had I known that I could write a book on such an elemtary topic as pivot tables and pivot charts... I'd have made some money too... Well that pisses me off...

Re:What IS a pivot table anyway?

[JamieF]

Bad analogy.

Movie trailers tell you the whole shitty story, so we don't have to watch shitty movies. Then movie sales plummet so prices are raised and they compensate with stupid explosions in surround sound and digital video instead of film.

Re:What IS a pivot table anyway?

[philml]

Mod this guy up. Spread the education ;-)

Re:What IS a pivot table anyway?

[johnlcallaway]

How about one Solaris sys admins can relate to??

I take the output of sar -d, pretty it up using PERL so that each line looks the same and the fields are tab separated (each line contains the date/time, partition, and the associated sar output). I then bring it into a spreadsheet using the 'import external data' feature (so I can point it to different files) and see what the relative queue lengths, service times, et.al. of all the disks are relative to each other and across a given time frame. It is very quick way to find hot spots on your disk subsystems without having to install any software except what comes with Solaris. It is often more useful to provide pretty pictures when asking for additional controllers and such.

With macros and ssh scripts, this is automated and takes only a few minutes.

If I was really motivated and had the need, I would dump the sar data into a database first so I could extract the data for any time frame very quickly. But I tend to use it only when there are performance problems. The entire spreadsheet has tabs for CPU, memory, paging, and semaphore stats and a single macro runs the scripts and refreshes the data tables.

Pivot tables to have one very big limitation though ... they are limited by the number of rows allowed in an Excel spreadsheet, so you can't easily analyze more than 65,000 data points. Don't think that is much?? There are 1440 minutes in a day, so a single day sar output of more than 45 partitions will overflow the spreadsheet (1440*45= 64800).

Re:What IS a pivot table anyway?

[abirdman]

You're absolutely correct. My problem is when that query you specified takes 15-60 minutes to complete (~300,000 records returned from a 5+ table join, then summarized in VB/FoxPro). The great thing about pivot tables is you can start with fairly raw data and you don't have to use more SQL to collapse it, you just drag fields around. I've been using them a lot in the last couple of days, to check the output of queries against the database to make sure they have the correct joins. Write the data to a CSV file, pick it up in Excel, apply a pivot table (it takes about 3 mouse clicks and a few mouse drags) and you've got totals. And you can slice and dice those totals however you need, dynamically.

Call me cynical, but anyone who's saavy enough to get SQL data into an Excel file can learn the pivot table features just by picking it off the (data) menu (YOU DON'T NEED A BOOK!!). But try it, it's amazing.

I have been working on creating 10 year history reports of insurance coverage, aka "exposure reports", gathered from three different databases, and running the output through pivot tables to identify problems, trends, changed product line definitions, and SQL mistakes.

The new history reports are supposed to match previous reports, but little of the infrastructure (product line definitions) for the previous reports (aside from the raw data) still exists. The SQL will output 40,000 to 60,000 records, which, with a minute or two of clicking and formatting in a pivot table can be summarized in a page or two. This feature allows looking at 60,000+ records, 10 years of history (one year each in 10 columns), and anywhere from a dozen to hundreds of product lines. It's easy (almost trivial) to define categories and subcategories, and even sub-sub-categories, and look at just a few or all of them, with automatic counts, sums, and totals. No group-by's or order-by's required in the SQL.

Consider this (auto parts store): Spreadsheet shows 10 years of individual sales transactions. Useful, but not informative. Pivot table shows Car Parts Sales, Years in 10 columns. Leftmost column shows make of car (Chevy, Ford, Dodge, etc.). Main part of pivot table shows summarized sales. Sales by Make of Car over last 10 years, great. Total time to define and format, 45 seconds or less.

If the underlying data contains it, add a second category column, with car's subsystem (fuel, exhaust, engine, electrical, body, interior, tires...). Drag that column over to the pivot table and you can see that (for example) people bought lots of engine parts for Chevys, and lots of tires for Fords. Drag off the make of car column, and see which car subsystem you sold the most parts for, irrespective of make of car. Or maybe drag the column for "salesperson" over and find out Jill sells more Chrysler parts than Fred.Three amazingly informative reports in seconds.

The great thing about Excel pivot tables is every category you drag onto the spreadsheet is "dynamic" meaning there will be a drop down list where you can click on or off any item on the list for instant checking. Just want to compare Fords and Chryslers? Less than a dozen clicks (depends on how many makes you have in your data). Just want to look at the last three years? Click, click, click. Done. File/Print/OK. Tires? Drag, click, you're done. Engine part sales by day of week? Drag, click, file, print, OK. Done.

The downsides? Everything is dynamic, and every change you make will require some reformatting (and new print settings) if you're sending this to management. If you need to reproduce the same report next year, save carefully. I don't use charts, but that's probably also a bit of a pain, because it's easier to change the pivot table definitions than the chart formatting. And of course, you're not going to be able to process more than 64,000 records in Excel, unless I've missed something.

Even though the tool is great, I still have to go back to work tomorrow and finish the reports. Even so, using pivot tables has considerably reduced

Re:What IS a pivot table anyway?

[AWhistler]

If you've ever used SAS, then you're familiar with PivotTables. PROC TABULATE is a very powerful function in SAS that does everything PivotTables do and more.

Re:What IS a pivot table anyway?

[thatnerdguy]

OT

...and now I am become FrontPage, destroyer of HTML...

and destroyer of good grammar

Re:What IS a pivot table anyway?

[jacobcaz]

Please enlighten me. I understand relational databases. 'push these buttons and out pops a report in this format is NOT an explanation'.

Okay - I'll take a stab at this. I've been using pivot tables rather heavily since being introduced to them in the last 12 months or so. The step up from a pivot table is a full-blown business intelligence (BI) tool.

Pivot tables allow you to "slice-n-dice" your data. Say you have a set of data that contains the following items: Business unit, sales person, sales territory, customer, customer buying group, order number, item information, cost and revenue. It's all just row after row of data, got it so far?

With SQL or just a big honkin' Excel sheet you can easily calculate data in one dimension (i.e. select/calculate all orders by salesperson X).

It's much more difficult to work in more than on dimension with data in this format. This is where pivot tables are really handy. Say you have the above data, but need to quickly produce a report to show revenue, by customer group, by item, by salesperson, sorted by date. That's more tricky.

This is a bit simplified; with a pivot table you simple select your data "elements" customer group, item and salesperson on the left, your date element on the top and then drop your "revenue" data element (subtract COGS from revenue) in the middle of the pivot and all the fields are automagically calculated. The best part is this all happens from your big, honkin' list of data. You didn't need to figure out any tricky SQL joins, etc when you got the data. You let the pivot table do the work.

Where it gets nice is the "slice-n-dice" capability I mentioned. Say you present this fancy report to your boss who says, "Great! Now show me the same by customer and not customer buying group!" All you have to do is replace the "customer group" element with the "customer" element and all the data is recalculated as fast as you can drop the fields. You didn't have to go back and touch your source data at all, it takes literally seconds to change how you represent the data.

There are a lot of OLAP (online analytical processing) tools out there. We use Cognos' Powerplay tool. It's nothing but a pivot table on steriods. It works on cubes (collections of data) which we produce from our ERP system. Then we can look at and present our data in the OLAP tool extremely easily. We even have executives using this tool (albeit at a less productive level than the hardcore BI geeks).

If you've ever seen someone whiz around with a pivot table or a BI tool you'll understand why I'm rather excited by all of this. It's powerful stuff and makes looking at data, trends in data and "drilling down" into data painless and rather fun. To see it for youself, whip up some bogus data in Excel and run the pivot table wizard (data - PivotTable and PivotChart Report...). It will walk you through your first pivot table in about 2 minutes!

Re:What IS a pivot table anyway?

[myukew]

true. its orthographic knowledge could have helped me. i don't use them and no one i know uses them. ergo: nobody uses them

Re:What IS a pivot table anyway?

[LinuxHam]

So essentially, a pivot table is just a tool for putting data from a RDBMS into the spreadsheet form you know and love.

And allows you to choose any subset of the fields of the spreadsheet for display in a mini-spreadsheet, lets you choose any field you want to serve as a "grouping agent" and dynamically updates subtotals for you. Plus, the subtotals and totals field can be changed to a variety of formulae, including Sums, Averages, even just counts. And you can choose whether or not to show every record that contributed to that subtotal, or just the subtotal line, and make that choice for *each* group.

So, to continue your example, you could check to see what the average price was for a particular service, or count the number of times a particular procedure was performed during a particular month, or each month, whatever. You can twist and mold your data and get views into trends and summaries like never before. Well, perhaps you could get at it in the past, but not with this level of ease and thoroughness.

And whoever suggested using awk against a csv to achieve this is so far off-base, its laughable.

Re:What IS a pivot table anyway?

[kin_korn_karn]

Everything about it, from the setting to the story, was horrific to me. I can't think of anything more frightening than being subjected to advertising no matter where you go or what you do.

Re:What IS a pivot table anyway?

[kin_korn_karn]

Cool, I got something right! That was twice this week.

It's almost like MS is trying to make Excel into a basic OLAP tool with this feature. Pretty soon it'll be a good enough word processor to write in :)

Re:What IS a pivot table anyway?

[Hognoxious]

Verbs involving a change of state or position use "to be" rather than "to have" as the auxiliary verb in the perfect tense in many of the English language's closest relatives, and I suspect this was the case in old English too. It still lingers in expressions such as "they were gone". So not necessarily bad grammer - archaic, maybe.

The post you are criticising parodies a quote (about the first atomic bomb) that was itself quoting, ultimately, some old Hindu scriptures.

Re:What IS a pivot table anyway?

[Angostura]

Thanks for the sensible and informative reply. In fact, I was attempting to be humorous.

I use 'attempting' advisedly.

OK, so what is it?

[DG]

OK then Mr Reviewer, how about cluing us in on what a Pivot Table is and why we might care?

DG

Re:OK, so what is it?

[hj43us]

Pivot tables allow you to cross count units withing categories. Imagine you hava a table of stock, you have shoes. Shoes have size number and model id. A pivot table can easily tell you the number of shoes of each size of each model. Of course you can get the same values out of simple SQL query (i.e. select count(*),model,size from shoes group by model,size) The difference is the pivot table will look like a double entry table instead of the long listing the query above might provide. I'm not sure I know enoguh to full a book about this. Cheers.

Re:OK, so what is it?

[FuzzyBad-Mofo]

If I get the gist of this right, PivotTables turn Excel into a kind of pretend-RDBMS? I imagine this would be good for the SQL-impaired, but I don't see any compelling reason personally..

Re:OK, so what is it?

[rainman_bc]

Pivot tables is like having a mini OLAP cube. In fact you can base a pivot table on an OLAP cube.

It allows a user-definable, multi dimensionable, dynamically alterable way to view, categorize, and report on your data.

Each field in a table or recordset is a dimension.

You can take each field and make it a row or column. Take temporal data for example. Let's say you have a sales table. You know the month. That's a good cantidate for a column. You know the sales branch, that's a good cantidate for the row. You know the product, that's a good subcateogory for the row.

So you produce a nice, temporal view of your data.

Now the pivot part. Let's say you instead want the rows by product, then by branch. You can simply drag the product to the left and you have just "pivoted the table.

You can drag fields around until you turn blue in the face. It's an amazing tool for lightweight data reporting.

Too bad most users are too stupid to understand them, and are too stupid to even try. I've shown many people how to use pivot tables, and they are too dumb to logic through this concept on their own. And they are PHB's mostly.

Re:OK, so what is it?

[Forbman]

OK, Mr. Lameass reader, do you also need a tutorial on how to start up IE, type in "google.com" in the address bar, and do a Google search for "excel pivot table"?

The first rule of PivotTables...

[Aardpig]

...is you do not tell anybody what PivotTables are.

Multi-dimensional databases

[gtrubetskoy]

For those who do not understand what multi-dimensional database is, here is how I explain it to myself:

A traditional relational database has two dimensions - rows and columns. Now if you take a table and make a copy of it to separate records by year (e.g. 2004data, 2003data, 2002data, etc.), you get a 3rd dimension. And if you were to take your entire database and make a copy of it to separate it by something else, you now have a 4th dimension. Of course the problem with all this would be inability to tie data in one database to data in another in an easy way.

A multi-dimensional database is something that allows you to add dimensions ad-infinitum _and_ query it in a clean way.

Re:Multi-dimensional databases

[wretched22]

I'm not sure that's the point. Excel is not a RDBMS and is not meant to be used as you would imply.

Re:Multi-dimensional databases

[gtrubetskoy]

I'm not sure that's the point. Excel is not a RDBMS and is not meant to be used as you would imply.

I'm only using an RDBMS as an example because it has rows and columns. To be excel-specific, substitute "table" with "sheet", "database" with "file".

Re:Multi-dimensional databases

[wretched22]

Oh, I see now.. I was just caught by the use of a database in your post... I actually went and looked up what a pivot table actually did.

Seems rather useful, now.

Take care.

Re:Multi-dimensional databases

[Anonymous Coward]

Congratulations, you are going to be featured as a QOTW on dbdebunk.com.

Re:Multi-dimensional databases

[slamb]

A traditional relational database has two dimensions - rows and columns.

I don't think that's a good way to look at it.

A relation (a single SQL table, view, query) has rows and columns, yes. But the column headings are fixed. So it's only one-dimensional.

However, this one-dimensional structure is adequate to represent N-dimensional data. (By having a table with a composite primary key of N columns.) It's pointless to "take a table and make a copy of it to separate records by year"; the relational way is to simply introduce a new column called "year". And you can have different tables to represent data of different arities. (One table to store stuff independent of year, one table to store stuff that is dependent on year and whatever else, one table to store stuff that is dependent only on year. However many tables it takes to properly normalize your data.)

One dimensional may not be adequate to understand N-dimensional data, though. If you make a query, you get a one-dimensional list. If you want to view a two-dimensional matrix, you need to use a matrix report/pivot table. This can make things much easier to look at and get a feel for.

Re:Multi-dimensional databases

[castrox]

Of course each row can also represent its own dimension. As such, a table can hold a space of multidimensional "coordinates" by using composite keys.

No need for multiple tables either, although that might be more pedagogical(?)..

E.g.:

Fields are: docID, wordID, frequency

1 1 3
1 2 15
1 3 7
2 1 4
2 3 13

This would represent a three-dimensional space seen with a wordID centric view, or a two-dimensional space seen with a docID centric view.

Re:Multi-dimensional databases

[Anonymous Coward]

He's probably and OOP fan, I bet.

Re:Multi-dimensional databases

[Forbman]

You are talking about the physical limitations of the table. It says nothing about the actual set up of the data. A pivot table is denormalized data.

It is just like saying arrays in any programming language are just a lazy syntactical trick, because they usually are mapped to a linear memory space anyways, and look what happens when you run this row-centric code vs column-centric code with this compiler.

The different field values in the table are the dimensions. So it's a hack to fit it into a two-dimensional storage space. BFD. It's no more of a hack that C provides to do a 3-dimesional array in a linear memory model.

Look a little bit deeper in your manuals about the "WITH CUBE" group by option (SS7, Ora8+). This is more what a pivot table provides. Internally it has the calculations for all combinations of the "dimension" columns stored in it, so that instead of running through the whole table to get a different analysis, it just pulls the relevant calculated fields for the desired sets of dimension fields.

As far as adding dimensions ad infinitum, it's easy enough to do it in SQL as well. Just add more dimension fields (and dimension tables) to the database.

Besides, for the scenario you talk about, SS7/2K, Oracle 8i/9i/10g, etc., let you "partition" tables on things like this. You make lots of separate tables, build a UNION query that ties them all together, and add constraints to the field they're separated by so that the query optimizer can look and quickly figure out that it only needs to hit a smaller partitioned table to get the data, rather than looking in the much larger whole tableset.

Oracle is nicer, because you can set up the partitioning to be essentially invisible, and much less specific, along the lines of (partition a = this month's data, part. B = the prev. 3 months before that, Part. c = the rest of the year, and Part. D - ... = years before that). Sql Server is not very abstract like that for partitioning schemes.

But this partitioning doesn't make the data "multi-dimensional". Sticking the year field into the table makes it multi-dimensional.

Whats the most power feature in Excel?

[stecoop]

I rank Pivot Tables about #1 in usefullness. Followed by autofilter, Vlookup, conditional Formating and regular old little VBS scripts. What are some of the higher features you guys use?

Re:Whats the most power feature in Excel?

[Galuvian]

Conditional Sums. I believe these are also called Array Forumulas. I'm having a hard time explaining what they do. When a Pivot Table won't work, I use these.

Re:Whats the most power feature in Excel?

[Rude Turnip]

I'm an "INDIRECT" junkie, myself. One of my jobs is to maintain a weekly snapshot of the universe of closed end funds (all 678 of them this week). Each week's worth of data (pulled in from a Bloomberg feed using the BLP function) is kept in a flat table in Excel with all the fields one would typically examine.

Each table of data is named for the week, ie "11-12-2004". The INDIRECT function lets a user type in the date he/she needs to look at and the model will pull in the data from the appropriate tab.

Or, using a combination of INDIRECT and MATCH, I can take two tables of data from two different sources and merge them together, as long as both tables have one column of roughly similar data (ie a list of names or serial numbers).

Re:Whats the most power feature in Excel?

[RandomWhiteMan]

Text to columns works really great, especially when you have a field for the date, but need it by month, or year. Concatenate I also use a lot, mostly in conjunction with text to columns. I pretty much just use excel to get large blocks of data formated, and then copy and past into Mini-Tab to run it through statistics formulas.

Re:Whats the most power feature in Excel?

[T3kno]

I like the export to xml feature so that I can xslt out all of the cruft and insert the data where it belongs, in PostgreSQL.

Re:Whats the most power feature in Excel?

[Anonymous Coward]

What else do I use? Umm, well, mostly
VB macros that allow Excel to open a
socket, write to C:\WINNT\system, and
manipulate registry keys.

I've found it essential that my spread sheet
program has the ability to send e-mail.
It's very useful for creating the
trojans I use to generate and sell spam
bot armies.

Re:Whats the most power feature in Excel?

[digital bath]

What are some of the higher features you guys use?

Clippy is my favorite feature, of course. And by "feature", I mean "thing that makes me want to shove my mouse through my monitor".

Re:Whats the most power feature in Excel?

[Anonymous Coward]

I really like the functions you've mentioned. I would have added weeknum(), but it seems to go out of sync in 2005.

Re:Whats the most power feature in Excel?

[bob beta]

Where you own it, and those grubby end users can pay homage to access it.

Yes. We understand.

Re:Whats the most power feature in Excel?

[Anonymous Coward]

"Restart" after playing a game and rebooting to a serious OS for real work.

Re:Whats the most power feature in Excel?

[Adhemar]

Apart from standard/basic spreadsheet functionality, "Solver" is definately the most useful feature.

For those who don't know it: you ask the computer to compute which value a certain cell should have in order for another cell to have a certain value.

Re:Whats the most power feature in Excel?

[soup_laser]

For me the most powerful feature would be the one two punch of

IF(minqty > qty ,minqty,INDEX(mincost:maxcost,1,MATCH(qty,minqty:m axqty,1)))

I was so happy to uncover this even though it replaced a macro I spent hours figuring out. Now I'm all excited to learn more about pivot tables!

ps. I use the above stated bit to match a needed quantity to the cost at that quantity. With a pivot table I could expand to multible vendors I'm guessing.

Re:Whats the most power feature in Excel?

[abirdman]

Thank you for that comment. It is hilarious and true. Hat's off to you, AC! It perfectly illustrates the power and the danger of Excel. It also made me laugh enough to alarm the neighbors.

Re:Whats the most power feature in Excel?

[Kiryat Malachi]

Exporting data to MATLAB.

Re:Whats the most power feature in Excel?

[Forbman]

=sumif(range-for-condition, condition-value, range-to-sum)

ex: =sumif("$a:$a","between 1990 and 2000", "$d:$d")

=countif() ..etc.

Array formulas are slightly different.

Re:Whats the most power feature in Excel?

[Forbman]

I feel sorry for you.

Wouldn't it just be easier to slurp the data into Access, and export an Excel spreadsheet? Methinks, yes, having had to do way too many Excel-based hacks that just work better in Access (or Sql Server, Oracle, etc).

dim fn as string
dim path as string
path="C:\*.txt"

fn = dir(path) 'gets the first file name that matches the pattern

while fn ""
'do something with fn

dir() 'gets the next file name match
wend

Re:Whats the most power feature in Excel?

[Rude Turnip]

Technically, what I am doing is exporting to a spreadsheet. The Bloomberg database is a huge frickin Oracle database. They provide add-ins to Excel to let me call up the data I'm looking for and dump it into the spreadsheet. For what I'm doing with the data at my office, keeping it in yet another database would be overkill, as the final product is two tables and two charts that are presented in a report. A few simple Excel functions does all I need and four weeks of data for a month is less than 4 megs.

Great Tool

[clinko]

Seriously, Pivottables are one of the greatest features of Excel.

Query your sql data, put a pivottable on that sheet, and you've amazed the highest of corporate bosses. I live by this thing.

Re:Great Tool

[happyfrogcow]

Query your sql data, put a pivottable on that sheet, and you've amazed the highest of corporate bosses. I live by this thing.


I'm sorry. I don't exist, goddamnit, to amaze my corporate bosses... uh just as second, boss wants to talk to me.

DAMNIT. Apprently I do exist for that purpose and that purpose only. And I've just been fired.

Re:Great Tool

[Zaranne]

Pivot tables (and charts) are the ONLY things in Excel worth using. Excel is nothing but glorified graph paper. Anything Excel can do a database can do better. And when the final product is given to said corporate boss, they can't *$%@ it up by sorting one column instead of the whole spreadsheet.

Re:Great Tool

[TheLink]

How about whipping up a cgi script to query the data and send the boss the URL?

What's a pivot table

[Pedrito]

I'm on the edge of my seat. Why would I want to read a book about Pivot Tables? It could be the best book every written in the history of Pivot Tables, but it may as well be a book on Gorglemopenchausers. I mean, I realize it's a book review, but even a sentence or a short paragraph saying, "A pivot table is basically XYZ" would have added a great deal of value to me as a reader.

Re:What's a pivot table

[Anonymous Coward]

but it may as well be a book on Gorglemopenchausers.

I've been looking for one for years! Where do I find this book on Gorglemopenchausers? And don't give me a link to a book on Gorglemopancheusers, I already made that $350 mistake, and B&N wouldn't give me a refund, because it was a "special order". Hot dog!

Re:What's a pivot table

[Anonymous Coward]

Watch the publishing announcements for my new book about Gorglemopenchausers, due out in January. A Gorglemopenchauser is much like a pivot table. Only better.

Re:What's a pivot table

[AppHack]

Of cource you'll never fully tap into the power of Pivot Tables until you create a pivot table of Gorglemopenchausers. Unfortunately a Complete Guide to Gorglemopenchausers is not scheduled to be published until 2006. However, the good news is this story will have been duped about 6 times before then so you'll have no trouble finding this review when you need it.

And then !

[ilikeitraw]

Pivot tables are priceless. I worked in a company with about 100 client service people, and they had no idea how they worked.
I would have to query the data out of oracle for them, and pivot the data for them, so they could send the report to the client.

Then, they would get HUGE commissions checks for pushing the "send" button in Outlook, and I would get nothing.

Then.... I quit.

Re:And then !

[AnyLoveIsGoodLove]

so did you go into sales?

Does OpenOffice support it?

[Spy der Mann]

Well I don't know about you guys, but the least thing I want is Microsoft products gaining more popularity.

Still, those pivot things do look nifty.

Re:Does OpenOffice support it?

[LnxAddct]

Read the last paragraph of the review.
Regards,
Steve

Demo

[wretched22]

This page explains what they do, at a very basic level.
http://www.cpearson.com/excel/pivots.htm

The Second rule of PivotTables...

[Anonymous Coward]

is you do NOT tell anybody what PivotTables are

Re:The Second rule of PivotTables...

[psetzer]

Rule #3 of Pivot Tables: There are n dimensions to a table.

Re:The Second rule of PivotTables...

[Anonymous Coward]

I can tell by the ammount of talk about pivot tables someone has been breaking the first two rules of pivot tables.

Dissing OpenOffice

[twistedcubic]

Nice review up to the last paragraph. Looks like the only reason you mentioned OpenOffice was to say that its analogous feature is "primitive" in comparison. Makes me wonder if that was the point of the entire review anyway. Hopefully someone can say something about Gnumeric and other specialized tools with respect to this TOTALLY AWESOME feature of MS Excel you so love. Here's one reason to exclude reviews of obscure MS Office features-- free alternatives without said useless features get called "primitive".

Re:Dissing OpenOffice

[0racle]

Many, many people live and die by pivot tables, well people that use Excel to any extent do, it is only obscure to people that use Excel as if it was a pad of paper. As such, any spreadsheet product that did not have a similar feature that was at least as usefull and powerfull would indeed be primative, and pretty much useless to anyone using Excel professionally. Don't let zealotry dictate what tools you use.

Re:Dissing OpenOffice

[Pedrito]

Many, many people live and die by pivot tables,

And I would suspect many more die trying to figure out what they are.

Re:Dissing OpenOffice

[RandomWhiteMan]

Hate to break it to you, but I don't call pivot tables useless. At least in Quality Engineering we process a lot of data, and pivot tables is one of the things we use on a day-to-day basis. It's actually listed on all our job reqs as a desired skill. I wish that in that one crap microcomputer course it seems everyone has to take in college... they would teach pivot tables. It would have saved me and a lot of other new hires a lot of time right off the bat. I'd love to use Open Office, but I rely on pivot tables to switch.

Re:Dissing OpenOffice

[pclminion]

The reviewer said that the DataPilot feature in OpenOffice (NOT OpenOffice itself) was primitive in comparison. Which it is.

You seem to have some issues of insecurity surrounding OpenOffice. Stop ranting and try reading what the guy said, instead of what you think he said.

Re:Dissing OpenOffice

[Daniel Boisvert]

I work in IT and finance, and -every- person I've ever discussed these things with hates them. They're nifty enough in concept, but there are two major problems with them:

1) They work great until you start adding data to 'em, and then they blow up in your face right before your deadline. This means you have to recreate your pivot table from scratch -every- time you want to use it. You can't add data to your source sheet and expect it to work consistently.

2) They're irritating as hell, largely because Excel's interface for them sucks. It's faster to do it up quickly in SQL than to use a pivot table. Hell, it's often faster to rearrange the data by hand using formulae and such than to use the damned pivot tables.

It's good to know from reading here that some folks actually seem to use them successfully, but they've quickly become the bane of my existence every time I've stumbled into them. I'd be pretty pleased if MS would just rip them out wholesale from Excel, and I can point to a whole bunch of finance/actuarial types who'd be right there with me cheering if they did.

In closing, I HATE THOSE FUCKING THINGS. :)

Re:Dissing OpenOffice

[kmankmankman2001]

Pivot table is hardly an 'obscure' feature. I'm as anti-Gates as the next fellow but I also try to remain objective in reviewing features of software. In general all of MS Office is horribly bloated and serves up features that a large portion of its users will never exploit . . . but Pivot Table is not one of those features. I'm surprised at the number of /. readers that don't know what it is - do you all live in caves are has your anti-MS bias really clouded your judgement that much?

Re:Dissing OpenOffice

[Tim C]

Well, personally, I'm a programmer; the only time I touch Excel is when someone else sends me a spreadsheet (that would often have been just as useful as a text document) or a csv file of data. Even then, I'm almost always looking, rather than manipulating it.

So no, I'd never heard of pivot tables either. I'm not particularly anti- or pro-anything, though; the right tool for the job, I think.

Re:Dissing OpenOffice

[Sai Babu]

"do you all live in caves are has your anti-MS bias really clouded your judgement that much?"

Perhaps it's just unfamiliar nomenclature and knowing another route to the same cathouse. It took forever for someone to actually explain what they are so perhaps the bias was anti-'button pushing'.

Re:Dissing OpenOffice

[Rich0]

Don't let zealotry dictate what tools you use.

Indeed - from everything I've read it seems like Excel is not really the ideal tool for this stuff most of the time either. I'd either use an Access-like tool for quick-and-dirty work, or a real RDBMS and a query tool.

Most of the examples in this discussion can be readily applied against millions of data points in only a few seconds with a line of SQL that would take a query tool all of 20 seconds to design. I wouldn't recommend trying that in Excel.

I'm not saying that pivot tables don't have their uses, but rather that the people who use them might find that they like Access-like tools better once they learn how to use them...

Re:Dissing OpenOffice

[jcr]

It sounds to me like you're dismissing the concept just because you've only used one flawed implementation.

Find yourself a copy of Lotus Improv (they shipped it for MS Windows 3.1, I believe), and try it out in a working app.

-jcr

Re:Dissing OpenOffice

[Daniel Boisvert]

Nah, more just taking the opportunity to kvetch about one of my most-despised features of Excel. I'll keep an eye out for a copy of Lotus Improv, though, and see if their implementation of pivot tables allows them to actually be useful.

Thanks!

Re:Dissing OpenOffice

[twistedcubic]

The reviewer said that the DataPilot feature in OpenOffice (NOT OpenOffice itself) was primitive in comparison. Which it is.

And what I said was

Looks like the only reason you mentioned OpenOffice was to say that its analogous feature is "primitive" in comparison.

Sorry, you can't read. Go back to school.

The Pareto Rule

[iztaru]

Like most people, I've only scratched the surface (well, maybe I gouged it a bit) of the capabilities of the Microsoft Office products.

Maybe it is because must of the people don't really need all that features. According with the pareto rule the 80% of the people will use only 20% of the included features. The computer systems should be built to address the needs of the customers, not to provide features and then let the users figure out what to do with them.

Re:The Pareto Rule

[bitmason]

>According with the pareto rule the 80% of the people will use only 20% of the included features.

The (frequently stated) problem with this observation is that which 20% varies from user to user. You see that in this discussion. Lots of people don't know what a Pivot Table is; quite a few others think it one of the most useful features of Excel. In my case, I imagine many people don't use the Reviewing features (change bars and the like) much if at all; I use them extensively.

Unfortunately, as noted in the review, these "specialty" features tend not to be that great in OpenOffice. But specialty shouldn't be confused with unimportant; they're very important to whoever uses them and lots of people use at least one specialty feature, just not all the same one.

Re:Gmail

[Anonymous Coward]

No, but that doesn't mean anything. It's not a single server. And gmail is beta.

Pivot Table History

[bstarrfield]

Pivot tables were originally developed in Lotus Improv, Lotus's incredibly advanced word processor for the NeXT machine. Lotus attempted to develop a new paradigm (can't believe I used that word) for spreadsheet interaction, something more sophisticated that +A1+@sum(B1..B3). Pivot tables were a component of this formulaless spreadsheet.

Improv was, is friggin' amazing.

I suppose this is another example of Microsoft getting credit for company's innovations?

Re:Pivot Table History

[ackthpt]

Pivot tables were originally developed in Lotus Improv

Well, that pretty much cements it, Microsoft is probably fondling a patent on these at this very moment, waiting to spring forth and attack some open source project, only to have prior art slapped in their faces.

Re:Pivot Table History

[gooman]

Talk about a flashback. I remember bringing a copy of Improv into a company I worked for and showing the Controller who was a Lotus-123 whiz, he was absolutely fascinated with it. For about a week he had that eyes glazed-over look that some gamers get. Improv was both innovative and unique. Too bad Lotus could never figure out how to market two spreadsheet products. I always wondered why they didn't just incorporate them into one great spreadsheet. Maybe, just maybe, Excel would not have taken over and kicked 123 to the curb.

Re:Pivot Table History

[thogard]

Who has the source code? Will it build as is under OS X?

Re:Pivot Table History

[Anonymous Coward]

Theres a typo in that 2nd link you mis-spelled "xerox" as "apple," a commoon mistake.

Re:Pivot Table History

[swillden]

Improv was both innovative and unique.

Agreed. Improv was awesome. I got a copy pre-installed on the NeXTstation I bought in 1991. It was a very cool machine, with an awesome OS, amazing development environment... everything about that machine was a decade ahead of its time. And Improv fit right in. Improv was so much fun to use I found myself inventing problems so that I could solve them.

Re:Pivot Table History

[Tim C]

I suppose this is another example of Microsoft getting credit for company's innovations?

No, it's a review of a book about a feature of Excel. I see nothing that states or implies that MS invented them, other than a note that the equivalent functionality in OpenOffice isn't as advanced or comprehensive.

True, it says that OO "mimics" many of the features of Office, but it's a fair point - it's often described as a (potential) Office killer...

Re:Pivot Table History

[Anonymous Coward]

Ah, tell me about it...

You wouldn't believe the amount of raised eyebrows I get when I tell people how much I enjoy using my Xerox PowerMac G5. ...

*groan*

Re:Pivot Table History

[T-Ranger]

I just read over the Wikipedia article. Is this not what Access is? Conceptually that is...

Re:Pivot Table History

[Anonymous Coward]

Yes, because PowerMac G5's are so innovative.

Re:Pivot Table History

[geg81]

I suppose this is another example of Microsoft getting credit for company's innovations [apple.com]?

I'm getting tired of just about every discussion about Microsoft being used as an opportunity by Apple fans to promote their favorite company. Keep that sort of stuff to the Apple groups, please. Whether or not Microsoft copied a feature from Lotus Improv has nothing to do with Apple.

Furthermore, it is stupid for Apple fans to point fingers when it comes to copying: without copying other companies' innovations, Apple wouldn't exist; they copied the very core of their platform from others (SRI, PARC, Alan Kay). Apple does have better taste than Microsoft in what they copy, but I hardly think they are more original.

Re:Pivot Table History

[Anonymous Coward]

Yeah, I wouldn't. I would have expected just rolled eyes and chuckles of disdain for being such a twat.

Re:Pivot Table History

[djmurdoch]

Pivot tables were originally developed in Lotus Improv,

They're a lot older than that -- they're just cross-tabulations with a GUI. SAS "proc tabulate" had these in the 80s (without the GUI).

Re:Pivot Table History

[smittyoneeach]

MS Access pivot tables are spelled "crosstab query".

Re:Pivot Table History

[T-Ranger]

After I posted that I fired up OOo, and saw what it did... A long time ago I did some Access work, so see it is very different. But they both do visual data manipulation. Pivot tables may be cool, but if you are doing that level of work in a spreadsheet, regardless of how good it is, you are using the wrong tool. (Not that Access would be the tool to use....). That said, Im the kinda guy who thinks a word processor should be used for word processing, and a desktop publishing system should be used for desktop publishing... Anything more complex then a letter should be done not in Word but Pagemaker.

Re:Pivot Table History

[linguae]

I suppose this is another example of Microsoft getting credit for company's innovations?

Improv wasn't an Apple innovation; Lotus Improv has nothing to do with Apple at all. Improv was a third-party application for NEXTSTEP.

Re:Pivot Table History

[Anonymous Coward]

Other companies copy each other all the time (linux copies windows).

Fact is its who takes the idea and makes the better product with it, and yes markets the product correctly. Simple.

Re:Pivot Table History

[Lars T.]

Just that Microsoft copied everything that Apple copied plus the stuff that Apple created from Apple. And Apple actually existed before the Mac. And without Apple asking Microsoft to write a spreadsheet for the Mac, there probably wouldn't be an Excel today.

Re:Pivot Table History

[jcr]

Improv was, is friggin' amazing.

That it is.. I still fire up a NeXT slab whenever I have any spreadsheet work to do.

You might remember Quantrix, from Lighthouse designs, which very closely matched Improv's feature set on NeXTSTEP. The person behind that app has re-written it in Java, with a number of new capabilities, and is selling under the "Quantrix" name.

-jcr

Re:Pivot Table History

[Anonymous Coward]

"Just that Microsoft copied everything that Apple copied plus the stuff that Apple created from Apple"

Well, then Microsoft Windows must be just as good as Macintosh today. So, why don't you just shut up about the Macintosh?

(Of course, I'm still wondering what you think Apple did create. The only original invention they made that I can think of is look-and-feel lawsuits, and those failed. Except for minor enhancements, just about else, they copied, stole, or bought up.)

"And Apple actually existed before the Mac."

Apple's first big success was the Apple II Plus, something that succeeded because of Microsoft Basic.

"And without Apple asking Microsoft to write a spreadsheet for the Mac, there probably wouldn't be an Excel today."

That about exhausts the level of Apple creativity: asking another company to write a clone (Excel) of someone else's great product (Visicalc).

Re:Pivot Table History

[Anonymous Coward]

Of course. Most people don't have a problem with that. It's the Macintosh zealots that keep posting inappropriate comments about how everybody supposedly copied everything from Apple that seem to have a problem with it. That's particularly ironic given that Apple is a company founded on pretty literally stealing its entire look and feel from another company.

Re:Pivot Table History

[LinuxHam]

if you are doing that level of work in a spreadsheet, regardless of how good it is, you are using the wrong tool

Might be a liiiiitle too judgmental here. I'm working on a 7 country, 4,000+ server consolidation deal right now, and to help sell that deal, I extensively used PivotTables. When you have close to 5,000 rows, 50 columns, and executive standing over you, asking "how many Suns do they have? how many boxes are running AIX? how much total CPU power is in Intel? How about Intel servers older than 3 years in England? And how many of those old Intel servers are we projecting to go to VMWare? How many VMWare servers would they need to buy to those those sessions?" followed immediately by, "Okay, cool, can you make a bar chart showing the total number of old Intel servers in each country, and split each bar in half, showing how many are going to be refreshed and how many are going to VMWare?" PivotTables are your God (or at least your "Day Saver").

I was the architect analyzing the servers for utilization and type of workload, and decided on whether or not to recommend VMWare for each individual box (and now I'm executing the consolidation). If I can answer the questions on the fly in a few seconds each, and create a chart customized to the exec's specific requirements in less under 2 minutes, AND answer technical questions about each server, then I am worth more than one person's salary. And although I am now up to over 200 VM guests in ESX, and the customer just bought 200 RHEL licenses for the ongoing porting efforts, I can't go to OOo until something that works as well as PivotTables and PivotCharts shows up there. I drive around with the famous LINUX license plate on the front of my car, but I'm sorry.. PivotTables and PivotCharts are amazing.

Re:Pivot Table History

[Lars T.]

You are assuming that Microsoft is good at copying. Not even at that.

(Of course, I'm still wondering what you think Apple did create. The only original invention they made that I can think of is look-and-feel lawsuits, and those failed. Except for minor enhancements, just about else, they copied, stole, or bought up.)

Double-clicking. Drag'n'Drop. And-so-on.

Apple's first big success was the Apple II Plus, something that succeeded because of Microsoft Basic.

Which is another thing Microsoft copied.

Re:Pivot Table History

[geg81]

Double-clicking. Drag'n'Drop.

Both of those were and still are rather questionable contributions from a usability point of view: they are exactly the areas where users have most problems with the mouse, and user interfaces are slowly abandoning both of them. The functionality itself, of course, existed prior to Apple anyway.

So, you have several decades of development of iconic user interfaces, object-oriented programming, constraint-based programming, and direct manipulation at other companies and labs, all of which Apple just copied. And then Apple comes up with a couple of questionable tweaks about when and people press the mouse button and you call that "innovation". I think that's roughly like putting fuzzy dice in a Ferrari and claiming you invented and built most of the car.

And-so-on.

Like WHAT?

[Microsoft Basic] Which is another thing Microsoft copied.

If there is one thing Microsoft really did by themselves, it's Microsoft Basic. Not that there would be anything wrong with Microsoft "copying" (i.e., independently reimplementing) things; that's how open systems and standards get created. In fact, the main problem with Microsoft is probably that they don't copy enough. Apple should copy even more as well, they should just stop making their incorrect claims that the stuff they copied is stuff they invented themselves.

Re:Pivot Table History

[T-Ranger]

Using linux and using PivotTables are not exclusive. OOo has pivot tables. I dont know about charts.

But you dont understand what I said. I diddnt say: data analysis is useless. What I said was that if you are doing data analysis that could be solved with PivotTables, then you should use a different tool entirely. If you are stuck on a MS platform, that tool might be Access. I would probabaly build some web based system, with a db backend, plotting with GNUPlot, but thats just me.

Re:Pivot Table History

[smittyoneeach]

What I said was that if you are doing data analysis that could be solved with PivotTables, then you should use a different tool entirely.

Hard to make a general statement like that. I'd be more comfortable with "beyond a certain complexity tipping point..."
For general business crap like quarterly sales by region, pivot tables have the virtue of being simple, straightforward, and giving the non-specialist that intangible feeling of adequacy, which is not to be underestimated.

Re:Pivot Table History

[T-Ranger]

Ok fair enough. How about: ... if you want to do that exactly once, $GRAPHICALTOOL is a good idea. But if you possibly would ever do the same thing again, use $REALSYSTEM...

Re:Pivot Table History

[Forbman]

You mean, an Access Cross-tab query? Yes and no.

An Access cross-tab query can only have one "column heading" field.

But Cross-tab queries are workable to help denormalize textual data.

Pivot tables are only for statistical analysis on numeric data.

Pivot tables are also only really useful when pulling data from MS-Query or OLAP cubes. The 64K-row limit in an Excel worksheet precludes using these as the basis for heavy-duty pivot table work.

What is the underlying data look like? Well, if you have a big summary query, and your SQL implementation supports the WITH CUBE statements (i.e., select x, y, z, sum(z) from the_table group by x, y, z WITH CUBE), then the output from running one of these is stored in memory. Rather than cycle through the dataset to resummarize the data, it has all the summaries already in it, and you really end up just pulling the various summary values.

Sure, Pivot tables will do the recalc occaisionally (like if you change your summary function), but it's in memory, and it's generally fast, much faster than rerunning the query.

Because it's in-memory, if you're hitting a million-row transactional table, you're probably gonna need a lot of RAM to work with it...

Re:Pivot Table History

[smittyoneeach]

Concur. I refer to this as the "acute vs. chronic" question. Technical types view the world as chronic, which is often a failing, in my experience. ;)

Summary of what a Pivot Table is

[Wrexen]

Say you have data like this:

Name Age Height (cm)
---------------------
Jane 22 174
Dick 22 212
Mary 24 150
Greg 24 198
Dave 23 244

You can use pivot tables to come up with tables like this (but with correctly calculated values :)):

Age Avg Height
--------------
22 190.5
23 244
24 174.5

For the most simple case, take any N-dimensional data, choose one of the dimensions to be your rows, choose one of the dimensions to be your columns, and choose a function of a dimension to be your field values. The more dimensions and data points you have, the more useful this becomes.

Re:Summary of what a Pivot Table is

[Anonymous Coward]

That's not a good example, because none of the data is used as the pivot table's column headings.

The example above could have been achiieved by simply subtotaling/consolidating the data.

Re:Summary of what a Pivot Table is

[UnknowingFool]

Here's another example where one of the dimensions is not a number

Region Product Sales
West Skirt $150
East Pants $160
West Pants $80
East Skirt $90

Pivot:

Region Skirt Pants
West $150 $80
East $90 $160

For those who don't deal with pivot tables, they are used primarily to organize data so that relationships between the dimensions can be analyzed. In the example above, you can see that sales for pants and skirts are higher in one region than the other. While you can see this relationship right away in the raw data, it beomes harder when you have much more data. Pivot provide a way to slice data

Re:Summary of what a Pivot Table is

[slamb]

There's a single-column primary key to that data (name), so it's one-dimensional. Your second view (average height by age) can be produced without a matrix report/pivot table. Where you do need pivot tables is where your data are more complex:

Name Age Height (cm)
---------------------
Jane 22 174
Jane 21 174
Jane 20 174
Jane 19 173
Jane 18 171
...
Dick 22 212
Dick 22 212
...
Dick 9 105
Mary 24 150
Greg 24 198
Dave 23 244

The above data points describe height by age and person. It's two-dimensional data, but you're showing it with only one dimension. (As it would be stored in a RDBMS.) You could make it easier to read by instead showing their ages horizontally and the people vertically (or vice versa) with a matrix of columns for their height at that age. That's what pivot tables are for.

Re:Summary of what a Pivot Table is

[Wrexen]

Hey slashbots, I realize this isn't the most complex thing you can do with a PivotTable. It's a simple example of something you can't quickly/easily do from just sorting and filtering. And only one of you has posted a better example so far. But thanks for the unconstructive criticism, fucktarts!

HTH
HAND

Re:Summary of what a Pivot Table is

[Anonymous Coward]

Your explanation suggests you do not/did not have a good understanding of the benefits of using pivot tables.

You neglected to explain the advantage over more conventional aggregation functions.

Clearly the moderators also lack understanding, hence the 5 score for such a weak example.

Re:Summary of what a Pivot Table is

[Anonymous Coward]

Just for the record, I found this thread to be the best explanation so far. You provided a good start, and the "unconstructive critics" filled in more details. So don't feel bad about it.

obligatory friends reference

[Anthony Boyd]

<ross>
Piv-ot. Piv-OT! PIVOT!!!
</ross>

Pivot table was the foosball table

[FerretFrottage]

where I last worked. Sure everyone one knew what you meant when you said "let's take a look at the data in a pivot table", but at least it sounded better then "let's foos". Plus you could claim to be the PTC (pivot table champion) and not know a damn thing about Excel.

Re:What are pivot tables you ask?

[Anonymous Coward]

Pivot Tables ARE PAINFUL!

That's all you need to know.

SQL?

[Anonymous Coward]

Surely SQL with group clauses is just as easy to use and does exactly the same thing?

Re:SQL?

[kmankmankman2001]

Sure, and I can do it in SAS as well. The relevance of either SQL or SAS to a book review on an Excel feature is . . .?

Re:SQL?

[Anonymous Coward]

SQL and SAS are for kids. I do it with grep, sort and sed.

Pivot tables are basically SQL aggregates?

[Anonymous Coward]

SELECT Sport, Quarter, Sum(Sales) FROM Data
ORDER BY Sport, Quarter ASC

The pivot table looks a little different, but that is essentially the same thing. Anything Pivot tables do that isn't possible in SQL?

Re:Pivot tables are basically SQL aggregates?

[Anonymous Coward]

If I understand the example of another /.er, it's more like a group by in SQL. Your example should be:
SELECT sport, quarter FROM data GROUP BY sport, quarter;

Re:Pivot tables are basically SQL aggregates?

[dogugotw]

I'm no M$ lover, but pivot tables beat the crap out of SQL. Why? Ease of use. To build a pivot table takes about, ohhh, 30 seconds if you're clueless. To then generate a graph, 1 second. Don't like the particular pivot/chart you have set up? No problem, grab the field you don't want, pull it out, drag a couple more in and you're done. They make it incredibly easy to visualize the data you're working with and take very little effort to set up or change. They also provide almost effort free data filtering and very simple but powerful drill down options. We use them to figure out failure trends on production data.

I use OO and the data pilot just ain't there yet.

If you've never used pivots (and have Excel) go play.

Are they perfect? No. I'd love to see an option to use something besides bar charts - XY plots with regressions would be the one big thing I'd like to see.

Dogu

Re:Pivot tables are basically SQL aggregates?

[DrinkDr.Pepper]

try it with more than 65536 rows.

Re:Pivot tables are basically SQL aggregates?

[grassy_knoll]

Anything Pivot tables do that isn't possible in SQL?

I've created Excel pivot tables and pivot charts as front ends to OLAP cubes stored in a MS SQL Server[1].

The main advantage is end-users can drag and drop predefined data elements to change the representation of data quickly. They can also choose which members of a data element to include / exclude.

So sure, lots of this can be done with SQL where clauses and group by/order by clauses. However, its a LOT faster with a pivot table / pivot chart.

As other posters have mentioned, this is one case where the GUI beats CLI.[2]

[1] All those Microsoft products... Karma, we hardly knew ya.

[2] This from a guy who's favorite IDE is still VIM.

Re:Pivot tables are basically SQL aggregates?

[Anonymous Coward]

My pain is 16k rows * 30 columns with partially calculated data from other sheets. Recalculation is kept for special scircumstances.

Re:Pivot tables are basically SQL aggregates?

[chthonicdaemon]

...this is one case where the GUI beats CLI.

I think the problem here is that the end users are already used to a spreadsheet-like interface. Many powerful and popular statistical packages use a command-driven interface which is much better suited to formulating abstract questions.

If you already have all your stuff in a spreadsheet, or are used to seeing it in a spreadsheet-like format, something like a pivot-table becomes the 'obvious' way to cross-function data. If, like me, all your data lives in abstract data sources and you are used to manipulating said data using language, the SQL or some other language-based option would be the obvious choice. One is not universally superior to the other.

Re:Pivot tables are basically SQL aggregates?

[Forbman]

you meant to put in "GROUP BY" instead of "ORDER BY"...

Most SQL processors will barf heavily on that SQL.

Oracle has ways to calculate the subtotal and overall total as well (without using WITH ROLLUP, COMPUTE or WITH CUBE)...

SQL Server only has WITH ROLLUP, COMPUTE and WITH CUBE. Of course, you could make a stored proc to do the same thing, and use it equivalently as a query, with Sql Server.

Re:Pivot tables are basically SQL aggregates?

[Forbman]

not a problem, though, if your datasource is a pre-stored OLAP cube, you're hitting a SQL Server with the OLAP tools (SS2K calls it Analysis Toolkit, or somethign like that), some other OLAP system that will work with MS-Query, or you're using MS-Query to run your queries...

Re:Pivot tables are basically SQL aggregates?

[dogugotw]

Let me ask this. Try creating a pivot-like system with drag and drop grahics using Excel. Let's face it, if you're using Excel for data analysis, you're NOT handling tons of data. The point of my post is to explain, to those who don't use pivot table, why they are so amazing.

Oracle has a moderately nice tool called Discoverer that provides pivot/chart functionality so if you need do handle a gazillion records, I think Oracle can handle the taks.

If there's an existing FLOSS system that is able to perform these functions, I'm all in!

Dogu

bring back the slashdot of old

[Anonymous Coward]

excel doesn't run on linux.. why the hell would i care?

Excel Feature?

[Geste]

Whaaaa? 38 pivot-table posts on /. and nobody has mentioned Lotus Improv yet? Fixed.

Re:Excel Feature?

[robvangelder]

Whaaaa? Talking about Excel on /. and nobody has mentioned Lotus 1-2-3 yet? With good reason.

Re:Excel Feature?

[gkuz]

Whaaa? Dozens (I'm too lazy to count) of posts on Lotus Improv and no mentions of Javelin?

Excel More Math Than a Brick House

[Anonymous Coward]

IF you ever had to code a linear system solver using Guassian ELimination for educational purposes you have a love hate relationship with pivoting:

Damn You Engineering Fundamentals!

Excel is for chemistry students and management types. Go Go gadget MATLAB.

A simple example of pivot tables

[sjbe]

The best way to understand pivot tables is probably by example. Lets say we have a spreadsheet with four categories of data: Country, Continent, GDP, and Population. These are listed in columns, with corresponding entries next to each other like so (apologies for bad formatting):

Country Continent GDP Pop
China Asia $1000 1,000,000,000
Japan Asia $5000 100,000,000
USA North Am $15000 280,000,000

Then lets say we are interested in finding the population of countries on our list which a located in asia. Pivot table provide a fast way to sort data as well as conduct simple mathematical operations on lists of data.

Using a pivot table I could end up with a matrix that looks like:

Continent Country Population
Asia China 1,000,000,000
Japan 100,000,000
North Am USA 280,000,000

Now I have the data sorted by continent. If I decide I'm not interested in population but instead in GDP, it is a simple drag and drop operation to get a table like:

Continent Country GDP
Asia China $1000
Japan $5000
North Am USA $15000

Basically pivot tables let you explore lists of data very quickly and efficiently. If you deal with lists of data regularly like I do, they are one of the most indespensible features in a spreadsheet. Excel has the best ones I've used but most modern spreadsheets have some version of them.

Re:A simple example of pivot tables

[slamb]

That's one-dimensional data. "Country" is a single-column primary key of that data. Each country exists in only one continent. Each country has only one population.

Here's a way to spot a one-dimensional list: if the number of columns is fixed, it's one-dimensional. ("Continent, Country, Population" is fixed; it's not dependent on your data.)

If you introduced a "year" column, then it would be two-dimensional data. China's population in 2003 was different than in 2002. And China's population in 2002 was different than Japan's. You need to specify both columns to fully identify a row.

You could then view it with country down the left and year along the top. If you add a country, the number of rows grows. If you add a year, the number of columns grows. Producing this two-dimensional view from one-dimensional list would require a matrix report/pivot table.

Notice that I'm making a distinction between two-dimensional data and a two-dimensional view. SQL tables can be considered a one-dimensional view of N-dimensional data. You can, through the right query, produce a view based on any or or two of those N dimensions. Only the two-dimensional ones report a pivot table/matrix report.

Re:A simple example of pivot tables

[sjbe]

That's one-dimensional data. "Country" is a single-column primary key of that data. Each country exists in only one continent. Each country has only one population.

I'm aware of that. Please note that I was responding to a question about what pivot tables were so I answered with the simplest possible example. There are obviously MANY more sophisticated things that can be done.

Re:A simple example of pivot tables

[slamb]

Please note that I was responding to a question about what pivot tables were so I answered with the simplest possible example. There are obviously MANY more sophisticated things that can be done.

But that's an example of something that can be done without using a pivot table; a simple query could produce that list. Thus, it is a poor example of what they're for.

Re:A simple example of pivot tables

[pointyhairedmba]

That seems somewhat arrogant. I work on the business side of a startup and I don't have time to run SQL queries on my data in order to get at actionable information. Pivot tables are very useful - if for no other reason than I use Excel for data analysis and not a SQL database.

Re:A simple example of pivot tables

[Anonymous Coward]

Thus, it is a poor example of what they're for.

Well then hotshot, why don't you take a few moments to bless us with your obviously Herculean knowledge of pivot tables, and post a nice example for the Slashdot readers?

Go on. We're waiting with baited breath...

Re:A simple example of pivot tables

[Anonymous Coward]

Are you really waiting with "baited breath" or are you possibly waiting with bated breath?

Re:A simple example of pivot tables

[aoteoroa]

Thanks sjbe.

So you can use a pivot table to quickly sort and group data. When does it make sense to use a pivot table instead of a simple sql query such as:

SELECT sum(Pop) FROM Country GROUP BY Continent ;
or
SELECT sum(GDP) FROM country GROUP BY Continent ;

Re:A simple example of pivot tables

[iocat]

I get it now. I actually need to do a lot of that and have been doing it by hand. Can anyone recommend a good book on Pivot Tables?

Re:A simple example of pivot tables

[MurphyZero]

When you've got your data in a spreadsheet instead of a database for one. If you've gone thru the efforts to create a database or have the ability to use SQL then you don't need pivot tables. But if you have your data in a spreadsheet, in Excel at least, the pivot table can be quite handy, and changing the way you look at the data can be easy. It's just another tool. It's basically for those times when you have too much data to look at simply and not enough to go ahead and build a proper database out of it. That's where Excel lives.

Re:A simple example of pivot tables

[sphealey]

That seems somewhat arrogant. I work on the business side of a startup and I don't have time to run SQL queries on my data in order to get at actionable information. Pivot tables are very useful - if for no other reason than I use Excel for data analysis and not a SQL database.

That is funny, because when I seen Finance struggling with pivot tables I can usually take their data, upload it into Oracle, query out the answer they need, and give them the results back for formatting in about 1/4 the time it would take them to calculate the answer in Excel.

Excel is a great tool for certain tasks. Since it is the only data manipulation tool many people know, it gets used for things it really shouldn't.

sPh

Re:A simple example of pivot tables

[Rich0]

I have to ditto that. I have coworkers (in borderline IT no less) who use Excel for almost everything. Once or twice I was handed tasks that they basically expected involved printing out spreadsheets and doing what was essentially an outer join by hand. And when they keep databases in Excel they scratch their heads and wonder why they have what amount to normalization issues with their data...

So, I just copied and pasted the whole mess into Access and did what would be a two day collation mess in about 20 minutes. (Sure, Oracle is a much nicer long-term tool, but Access is great for quick-and-dirty (emphasis on dirty).)

Some types of financial figures are fairly easy to work with in a pivot tool (there are a bunch on the market). However, if you really want to understand your data, learning SQL is invaluable!

Re:A simple example of pivot tables

[slamb]

Well then hotshot, why don't you take a few moments to bless us with your obviously Herculean knowledge of pivot tables, and post a nice example for the Slashdot readers?

Umm, I posted three of them.

We're waiting with baited breath...

It's "bated breath".

Re:A simple example of pivot tables

[sjbe]

Can anyone recommend a good book on Pivot Tables?

I'd recommend Excel Data Analysis (ISBN 0-7645-3754-7) by Jinjer Simon to get started. It's a pretty good book and breaks up topics into questions you might be likely to ask and has a good solid 30 pages on Pivot Tables and Charts.

Re:A simple example of pivot tables

[sjbe]

When does it make sense to use a pivot table instead of a simple sql query

Well for one, when you are using Excel instead of a database! :-) Typically I have data provided to me in spreadsheet form and its often easier just to work with it there. Sure, I could import it into a database but most of the time that isn't necessary.

If you do have it in a database, that's great but once you've selected the data you want with a query what are you going to do with it? The query is just the first step. You might have millions of records you need to summarize. Do you want to present the data by averages or maybe the standard deviations? Do you want a graph? Do you want to have subtotals? Pivot Tables let you do all this (and much more) quickly off of queries from a database (or lists in excel) and get good looking output without having to design a report ahead of time. Think of Pivot Tables as an automatic report generator. (they're not exactly but it's not a bad way to think of them)

Re:A simple example of pivot tables

[TomV]

Now you've hit the nail - it doesn't make sense in that precise situation. When it *does* make sense is when the person to whom you give this data then immediately says "that's great. Now could we see how that breaks down by Age?", so you go off and rewrite the query to GROUP BY Age mod 10, say, and go back and show the results, to be met with "Thanks. That's really useful. It would be even better if we could split it into male and female."

So, back to the SQL prompt, GROUP BY sexMF ORDER BY sexMF, back to the end-user, to the inevitable "Thanks, that's just perfect. But... how do the ages and sexes total up across all the countries in Sales Region 3?".

The bonus, for our company anyway, of the Pivot table is that we can write a single query to bring back lots of raw data without any of the GROUP BY clauses in place, and then the Account Managers or Operations Supervisors or whoever can use the Pivot Table feature to choose their own GROUPings and subtotals, counts, averages, sort orders and so forth, in their own time, generating dozens of business-useful reports from a single half-hour chunk of chargeable IT time. And since IT time is recharged at about ten times the price for Account Management time, the Pivot Table approach saves us a lot of time and a lot of expensive IT time to use on more profitable work.

Re:A simple example of pivot tables

[LinuxHam]

That is funny, because when I seen Finance struggling with pivot tables I can usually take their data, upload it into Oracle, query out the answer they need, and give them the results back for formatting in about 1/4 the time it would take them to calculate the answer in Excel.

What you see are people very poorly trained in the use of their basic office tools. Yes you can do it in 1/4 of the time it takes *them* to do it in Excel. That's not a limitation of Excel, however. They need to take a class.

Re:A simple example of pivot tables

[Forbman]

SQL tables can be considered a one-dimensional view of N-dimensional data. You can, through the right query, produce a view based on any or or two of those N dimensions. Only the two-dimensional ones report a pivot table/matrix report. ...yes, and no. It depends on the source.

select country, industry_grp, year, sum(population) pop_sum, sum(gdp) gdp_sum
into census_cube
from census_table
group by country, industry_grp, year
WITH CUBE ...is a multi-dimensional table... The original table was multidimensional too (country, year dimensions).

What you are saying is that "a multi-dimensional array isn't really multi-dimensional, because it's mapped to a linear memory space". It's all true at the physical level, but in practice, it's a multi-dimensional structure.

Like the pivot table, the goal is to store (all of) the summary results into a storage structure (server table, pivot table memory), to hasten analysis so that query time over the parent dataset is reduced to a single action, and retrieving the summary results goes from walking through a 100K (or 1M) transactional table to querying a much smaller table that already has your results.

This is sort of the point anyways of a data warehouse, products like Hyperion, Business Objects, etc.

Re:A simple example of pivot tables

[slamb]

Interesting. I'd never seen that "with cube" syntax before. I have some queries that I might be able to simplify with that operator, so thanks for the pointer. Looks like both Microsoft SQL Server and Oracle 8i or later have that syntax, and SQL-99 has a similar "group by cube" thing. (And, thus, PostgreSQL will have it soon if it doesn't already. I saw patches but haven't seen yet if they've been accepted.)

What you are saying is that "a multi-dimensional array isn't really multi-dimensional, because it's mapped to a linear memory space".

I'm more talking about its presentation to you, actually. When I say "view", I mean its appearance; I'm not describing a SQL view. A relation (SQL table, query, view) can certainly hold N-dimensional data, and in such a way that you can select whatever dimension(s) you want. The only real difficulty is presenting it to the user properly; just listing out the query results is presenting it in a one-dimensional fashion. That's the point I was trying to make, and it doesn't sound like you're disputing it.

Re:A simple example of pivot tables

[sphealey]

What you see are people very poorly trained in the use of their basic office tools. Yes you can do it in 1/4 of the time it takes *them* to do it in Excel. That's not a limitation of Excel, however. They need to take a class.

I disagree, actually. My post included (although of course you have no way of knowing so) some very sophisticated users of Excel. There are just fundamental limitations of the product - no reflection on Excel or its users, just reality.

sPh

Re:A simple example of pivot tables

[kubrick]

Excel is a great tool for certain tasks. Since it is the only data manipulation tool many people know, it gets used for things it really shouldn't.

I saw this linked to from joelonsoftware.com about a year ago.

Made me laugh, because I was suffering from having to convert 20,000 row .xls pseudo-databases at the time.

Time to shine.

[Associate]

OK, got it. Think I'll make my boss' head explode next week. Think Scotty appearing to be a miracle worker to Cpt Kirk.

What I would like to see in SQL one day

[Anonymous Coward]

I would like a pivot table SQL aggregation function.

Something like

SELECT ROWS:GeneralLegderCode,
COLUMNS:Year,Month,
DATA:SUM(Value)
FROM GeneralLedgerTransactions

Granted it would demand careful programming sice the program would never know what fields would be contained in the results.

Does that maks sense?

Re:What I would like to see in SQL one day

[xv4n]

Try MDX first.

Re:What I would like to see in SQL one day

[meringuoid]

I would like a pivot table SQL aggregation function.

Unsurprisingly, they've got it in Access. There's a 'crosstab query', which is basically a regular SELECT query with pivot table processing done to it. In SQL this appears as a 'PIVOT ON' clause, but I never did quite work out how to use it...

Re:What I would like to see in SQL one day

[DogDude]

Jesus Christ. It's called "GROUP BY".

Don't people know how to use SQL any more?? When I learned SQL (granted, it was from a DBA and developer who knew his shit), how to manipulate data like this is what made SQL so cool and useful.

Re:What I would like to see in SQL one day

[Anonymous Coward]

Now you are guilty of making an oversimplification, probably based on lack of experience.

I don't have the time to spell it out. Life is too short to educate the ignorant.

Re:What I would like to see in SQL one day

[electroniceric]

For all you Access-bashers out there, the PIVOT keyword was exclusive to Jet SQL (and I believe it still is - it will be added to the next release of SQL Server). Please correct me if I'm wrong.

While it's obviously possible to build stored procs that do what PIVOT does (I'll likely be doing this in Postgres), you have to give credit to the Access team for being ahead of the curve on that one. Unfortunately, its syntax is pretty hard to understand. I can build those crosstab queries in Access using the builder, but I'll be damned if I can understand how to do anything with PIVOT.

Pivot Tables

[Eberlin]

For those that don't know, this entire book is dedicated to that ever-essential dining room furniture known as a lazy susan.

It sits on top of your dining table and it spins around and around. People put food on this spinning table so diners don't have to go "please pass the (insert supposedly ingestible food item here)"

I guess its inclusion in Excel was just a step above the flight-sim easter egg they once had. If Mozilla can include a kitchen sink, the MS folks took the liberty of including a lazy susan. Er, sorry, pivot table.

Look for OpenOffice to include a Pivot Chair in the next release. I, for one, can't wait to tell them MS folks to implement their own pivot chair -- so they can sit and spin, baby, yeah!

OWC covered?

[Kenja]

Are the object web components covered? I use them at work to genereate web based pivot tables and the API is far from fully documented.

Re:OWC covered?

[mrfunky405]

Let me second your cry for help here. The documentation is pathetic.

Re:OWC covered?

[Anonymous Coward]

Dont' think so, OWC and excel use different engines for pivot tables. I'm sure a lot of the book would apply anyways. Yes the OWC api is another great MS "we built it but why should we tell anyone how to use it?" exercise.

Lotus Improv - Pivot Tables on Steroids

[crunchyprogramr]

I'll be happy when Excel lets me work with pivot table data the way Lotus Improv did.

Ughhh...

[Darth_Burrito]

Just sort of a general comment ... I think the capability of office suites to do complicated things is directly proportional to the amount of pain felt by regular IT staff. Seriously, it's nice that people can do all sorts of neat stuff and track data outside of the system... because, often due to time constraints, it's not always possible for IT staff to facilitate all requests... but then they come ask us for an on-demand parameterized report that works off an ms sql server database, mysql, access, and excel.... and we get stuck doing it regardless of the relative value proposition and it takes a lot of time and ends up crashing when someone locks the excel file or fubar's the access database... and it's our fault.

Part of what makes it so painful is you get stuck spending your time trying to figure out how to do something the-right-way in excel when the same task would be trivial in a database. It's not just a matter of burning everything to the ground because, if you take that approach, you are forced into accepting responsibility for rewriting the thing. Some of the stuff I've seen people make is just so thoroughly messed up as to be conceptually, not just technically, beyond redemption.

Of course, the flip side is, if people can do more stuff on their own, that's one less thing they have to bug me about... but the things they do hit you with become all the more painful.

deep hurting...
sandstorm....

Re:Ughhh...

[Anonymous Coward]

Well, before i make my next comment, let me add that i am part of the IT staff where i work.

I hate it when IT people whinge about the demands of us, or dictate what the 'other people' can and can't do. That is our job, we support them. They make money (hopefully), and pay us. When we are developing products here it is so easy to fall into the 'they are only users' way of thinking.

Try and remember to say to yourself, at least once a day 'They pay my wages'...

A PIVOT TABLE IS...

[bmalia]

A link for those of you who are too lazy to google the info themselves...

Patent issues

[Anonymous Coward]

Aren't pivot tables patented?

PIvot tables? Yawn

[pclminion]

From what I can tell, these "pivot tables" are just a primitive form of concept analysis (data mining).

Anybody reading this article who actually thinks these pivot tables sound "powerful" should look into some of the real row-based data mining tools out there. For starters I suggest looking at Weka and Orange.

Weka in particular is extremely easy to use and you don't have to be a researcher to figure it out.

Re:PIvot tables? Yawn

[squidfood]

Anybody reading this article who actually thinks these pivot tables sound "powerful" should look into some of the real row-based data mining tools out there.

Good lord, I think I can deduct an hour of slashdot browsing. Extremely useful, thanks!

Re:PIvot tables? Yawn

[Anonymous Coward]

Sure, they are a primitive tool for data mining, but look at all the people unable to grasp the concept, stories of PHBs and "finance types" who rely on the computer geek to use this Excel feature... it makes one wonder how we ever got out of caves.

Description of a Pivot Table

[chrisatslashdot]

Suppose a database of widget info...cost, model, storage location, color, etc.

A pivot table:

• A rectangular table with rows and columns
• Along the top you some pick attribute(s) of the widget
• Along the left side you pick other attribute(s) of the widget
• You pick what goes into the interior of the table and how it is summurized

So you could compare color(along top) with location(along left side) and choose to see the sum of the inventory levels in the interior.

or...

SELECT color,
location,
SUM(inventory)
FROM table
GROUP BY color, location

But in matrix form so that you end you with one cell for each color+location instead of one record. So the size of the matrix grows approximately by a power of 0.5 with respect to the number of results.

Database output:
location |color | inventory
warehouse A| red | 100
warehouse A| blue | 123
warehouse B| red | 0
warehouse B| blue | 50

Pivot Table
------------red blue
warehouse A 100 123
warehouse B 0 50

When viewing the page with Firefox . . .

[acceleriter]

. . . it says at the top

Warning: You are viewing this page with an unsupported Web browser. This Web site works best with Microsoft Internet Explorer 5.01 or later or Netscape Navigator 6.0 or later.

Would you like some FUD with those tips?

Please rename the article!

[Anonymous Coward]

ATTN: Please rename the article to "A Complete Guide to Shit Nobody Cares About".

Thank you!!

Hmm...

[Pivot]

I must say that I indeed have this really nice solid wood table that I got a ikea. I use it mostly for food though, not so much numbers..

Ok

[Hard_Code]

Who else thought the entry was about some form of air hockey, foosball or pinball machine?

Pivot table info and resource

[kendor]

There seems to be some natural and/or knee-jerk confusion that pivot tables are some Microsoft-only creation. For some folks that naturally means that they'll never want to use them. That's too bad, a little bit like concluding that referential integrity or relational table structures are "so Microsoft" just because they're discussed in SQL Server documentation and literature.

Best discussion I've read of pivot tables as a generic tool for managing data appears in O'reilly's thin "Transact SQL Cookbook". Excellent book. The first few chapters are devoted to pivot tables alone. According to the authors, PTs are fundamental to solving many data storage and display problems. Have a read, then post more. :)

Pivot Tables Problem

[J-B0nd]

I worked on a project this summer where pivot tables were used to tally up how many person hours of specified job types were needed for a project. The problem was that it was sorted by person hours per month up to 15 years, and the starting year could be changed. When the year was changed, the tables didn't update correctly. I ended up having to use a lot of vba code to rebuild the tables from scratch every time the year changed. This was using Excel 2000. Does the book address issues like this one? Did anyone else ever have a problem like this one?

Re:Pivot Tables Problem

[Anonymous Coward]

This problem is common amonst those who use a spreadsheet when they would be better served with a database. A spreadsheet makes a great gui for the data output from a database, but generally makes a fragile database in and of itself.

This usually happens because someone wants to enter their initial data into a gui and it never dawns on them how this data will grow. Spreadsheets are awesome for data display, but they always suck as databases, which is what they usually evolve into.

Re:Pivot Tables Problem

[J-B0nd]

The development team for this tool looked at creating a database app instead of overhauling the spreadsheet app, and also looked at rewriting everything from scratch in .NET and putting it on the corporate intranet. However, once the pros and cons of all three approaches were considered, the spreadsheet was the most practical way to go. This left us with problems like the pivot tables updating.

Hey Cool!

[Anonymous Coward]

I just clicked on the first link after searching for 'pivot tool tutorial' --it isn't slashdotted anymore. Then I fired up openoffice and went through the whole tutorial. It works *EXACTLY* the same, (except that OpenOffice on Linux is faster than Office on windows). Every part of the tutorial worked otherwise exactly (exactly) the same. All of the prompts looked the same, the data was arranged, highlighed and behaved the same. Woot!

Re:Hey Cool!

[Rhinobird]

For those looking for Pivot Tables in Open Office, they are for some strange reason called "DataPilot"

Quantrix does it better

[rjrjr]

If you like pivot tables, you'll love Quantrix. It's a multi-dimensional spreadsheet that picks up the ball that Lotus Improv dropped.

Disclaimer: I'm not a shill--not even a customer, in fact. But I'm a friend of the author, and was a contributor to Quantrix's NeXTstep based predecessor.

Consorting with the Enemy?

[mi]

capabilities of the Microsoft Office products.

You, traitor...

cognos powerplay

[Mr 44]

Cognos Powerplay does this too, and even better than excel.

Re:cognos powerplay

[dimrrr]

This must be a troll. Powerplay Transformer is a textbook example of how not to do a UI. Ever saw a program that shows a "do you really want to cancel?" popup when you try to cancel out of a properties dialog? How about this syntax in expression editor:

if (foo isnull) then (1) else (0)

Actually, this doesn't work, but this does:

if (foo isnull ) then (1) else (0)

Need more examples?

what about sc?

[OrangeTide]

sc can do this too. Actually any spreadsheet package from the 1980s can create a spreadsheet using data from another spreadsheet. It works the same way as equations do, except accross spreadsheets. big deal.

the only real difference is that PivotTable in excel can be done automatically based on the names of columns, instead of having to go to the effort of writing a 3-5 line awk or perl script.

btw- I analyze most data using gnuplot. it's an extremely powerful and easy to use package. But graphs analysis is a different sort of thing than what the poster of the article wants to do.

I can do better things in Python ...

[Anonymous Coward]

Sorry but anything you can do with Pivot Tables
I can do better in Python ...

Not mentioning that copying and pasting Pivot Table data has often completely unexpected results ...

I am not impressed and I won't criple my abilities ... I am sticking with Python ...

Pivot tables originated in Improv

[QuietLagoon]

I'd really like to know what the hell a pivot table is.

Pivot tables are a feature of Excel that Microsoft stole from Lotus' Improv.

Oh. so thats what it is.

[Retep Vosnul]

So it's an enduser thingy.
Thanks for pointing that out so clearly.

Now I will just skip on and hope my users don't catch on to this thing and come to my office asking me how the freak they can use pivot tables.

( They come in with this crazy idea that I know excel and think I can show tham the magic trick in anything.)
Excel is by far the most powerfull counter productive tool in the hands of a office worker.

Thanks man, you really did us admins a favor.

I blame the parents

[Random_Goblin]

I think one of microsoft's biggest problems with Office is the Users of office (ie the general public).

The standard way of "improving" your software is to go and talk to the users, find out what they need, what they like, what they don't like, and put it into your next version.

This is where microsoft's biggest problem is... its users don't know what they want, because most of them don't know what their program can do, or even what it ought to do. It's just the one on their desk top that they are assumed to know how to use.

Photoshop and AutoCAD users (for example) know exactly what their application can do, and what it would be really usefull if it could do, and they tend to get these features in their upgrades.

Office users mostly don't have a clue what they want ... and so they get different animations for clippy, menus that hide from you so you can't remember where things are, images that once inserted are a nightmare to tweak etc etc etc.

And i can't believe, it's because microsoft just want to piss people off, somebody has to ask for these things, I'm sure they have focus group, after focus group saying how it would be nice if clippy could be a hedgehog now and then...

I feel so sorry for the developers, office can do some absolutely amazing things... It's just that 90% of its users use 1% of it's functionality...and then demand more "wordart".

Excel and Access are the two least clippified applications in the suite, probably because they have the more specialised users who actually USE the programs instead of playing with them like most users do. (of course most access users want to be using something else... but still!)

Not true

[nurb432]

While i risk feeding the trolls, for a real business with real users, pivot table are rather important.

Sure there are other tools, but this gets the job done for a lot less cost.

Pivot Tables and 1d10ts

[sobriquet]

Pivot tables are a very useful tool. Unfortunately, most people just don't get them. By them, I mean the average business person. In my experience at a mid-sized retail shop, pivot tables could be an incredibly useful tool for the business types. Unfortunately, the same people that will spend hours playing with simple formulas, colored columns, and font sizes in Excel will not expend the effort to learn how to use a pivot table. As a result, I spend a lot of time coding to give them the same things they would get with pivot tables. It always depresses me when one won't put forth an effort to learn the ins and outs of a tool that is supposedly critical to their work. Oh well, it keeps me in a job.

Re:Pivot Tables and 1d10ts

[cinnamon colbert]

maybe most people dont get them because the explanations in books are so bad. I have read the pivot talbe chapter in several well reviewed excel books, and the eplanations are dog poop.

Evil

[Shadow_139]

I have just spent 4 hours in work on fucking evil Pivot Tables they are crapper the crystal reports... There just an other way of displaying shity flat excel/database in a "nicer" and "user friendly" way., but they suck to setup..... ---- Noodle http://www.tbns.net/georgis./jthm/icons/teddynny.g if

It appears you've been moderated by the MS drones

[dsb]

I have recently did a mathematical paper in LaTeX and included a couple approaches of converting LaTeX to other text processors. My HTML conversion process worked fairly well using OpenOffice. But then when I took that OO doc which I save as a .doc file within OO and opened it up with a Word in both Windows and Mac versions, my maths were completely mangled, missing, or destroyed. I am now left with attempting to do RTF conversion, which prelim results show to handle maths a little better.

That one ordeal I went through just helps re-enforce my position to side along with you on the matter. I don't know much anything about these pivot things, but it seems that it can be easily achieved using the OO alternative since it's basic matrix math manipulation. Apparently, the slashdot drones have succumbed to the mindless borg advertising that is and the sheer numbers rule the moderation system of slashbots. And that is notwithstanding anything to say about the editors. I feel for ya bro!

FWIW, set your preferences for Trolls at +5 modifier, and you'll find some interesting posts!

So what happened to Improv?

[snStarter]

This sounds like a nifty product. Since it was written for NeXT I wonder if the sources are still around and usefully compilable for OS X.

Which brings up the suite of office software that was developed for NeXT and which is owned by Sun but which will never see the light of day. IT should port quite easily.

Sigh.

Really... what's so revolutionary

[dandyroot]

Give me a break, pivot tables are nothing more than a canned relational database query.

great! =)

[codergeek42]

Now show me how to do it in OpenOffice.org and Il be a happy camper...

No, they are not

[Anonymous Coward]

Pivot tables allow you to turn what had been groups of rows into columns in your spreadsheet. There is no way to do this in SQL.

In SQL you can duplicate all of the data manipulations, and have all of the same numbers somewhere in the query output. But you can't put it into a convenient display. Spreadsheets are all about putting things into convenient form.

Of course putting things into a convenient display where what it says is obvious at a glance is the difference between data and information. Customers pay for information, not data. Underestimating the value of presentation is a classic shortcoming of techies. (I should know, I am one.)

If you want to understand them better, talk to someone whose job is to understand data, for instance a financial analyst. I've met very few programmers who understands what pivot tables are for or why you want to use one. Every analyst that I know is a wizard with them.

obviously OT

[louden obscure]

thank god that porn article showed up, otherwise i woulda hadda use my mod points on either pivot tables or DIY cyclotrons.

Excel Pivot Tables Are Limited

[tgraupmann]

Excel Pivot tables pretty much suck. With Excel 2000 you can only pivot on a tiny 8k maximum worth of unique records. With Excel 2003 it goes up to 32k unique records. I have 1G of RAM, so this is a useless amount. Pivot tables are useless for everything but a grocery list for a block party.

Lighthouse Design?

[mosel-saar-ruwer]

Which brings up the suite of office software that was developed for NeXT and which is owned by Sun but which will never see the light of day. IT should port quite easily.

PC Expo: Sun buys object developer
Lighthouse Design noted for its OpenStep tools
June 1996
http://sunsite.uakom.sk/sunworldonline/swol-06-199 6/swol-06-lighthouse.html

Sun steps up acquisition efforts
April 24, 2001
Bungled deal
Jonathan Schwartz, who heads Sun's 63-person acquisitions team, says Sun bungled the purchase of his company, Lighthouse Design, in 1996.

"There was no integration," Schwartz said. "The only things that changed were the business cards and the T-shirts."
http://news.com.com/2009-1001-256399.html?legacy=c net

Lol...Pivot tables are old news but still powerful

[Anonymous Coward]

Our company has been using pivot tables in excel for years. Its always been a very powerful feature of excel, and tied in with access or a data extract from your database you have the ability to create very elaborate drilldown reports.

SysAdmins, please play dumb about Pivot Tables...

[ManyLostPackets]

I don't don't know about anyone else, but I was foolish enough to show seasoned analysts the minor miricle of pivot tables.

Guess who the new data analyst is?
(along with the e-mail guy, the PBX guy, script monkey, tech support dude, etc...

Grokk-me-not

Much easier than the old way

[Animats]

Back in the old days, we had to run all the cards through the sorter to sort on the desired field. Then we'd wire a IBM 407 tabulator board to do a control break on the desired field, and print the total. If someone wanted an average, we had to cable up the 519 summary punch to the 407, and wire a tabulator board to send the total and count to the punch. The 407 couldn't divide, so then we'd run the summary cards through the 602A multiplier to do the divide. The 602A would do A=B/C and punch A into the same card that contained B and C. Then the summary cards could be resorted into the desired report order and run through the 407 with yet another board to generate the report.

You guys have it easy.

Funny, TFA also applies to ...

[Anonymous Coward]

When you read this book, I would recommend that you sit at your computer to try these techniques as you read them. I tried to just read the book at first, but you really get itchy to try each feature out. Take it in sequence, as there is a definite building from one chapter to the next.


This also applies to this site

Sorry, could not resist :-)

Kind of like SQL view, I think

[rsmeds]

As I understand (although I might have misunderstood) a pivot table is kind of the Excel equivalent of a 'view' in an SQL database.

Amen, brother!

[vogon jeltz]

Unfortunately my mod points expired yesterday ...

Gnuplot, Python, awk, bash, and sometimes even C make your day when analyzing megabytes of data.

I ususally find it more convenient to come up with some hacked-up solution rather than to study Excel or OOo documentation only to realize later that Excel doesn't like the raw ASCII format my data is saved in.

please explain the significance

[Anonymous Coward]

I am wanting to know the significance of the data selected for the example.
Can somebody please explain what these numbers would mean in relation to the Kyoto Protocol not being signed by the U.S.?

I'm beginning to think that pretty soon the sleeping dragon will wake...

awk

[Anonymous Coward]

I just tried the tool under objection. I tried to make a "pivot table" of a 1.6M CSV file under Open Office. It also seems like OO cannot read files with more than 32000 records. There should have been approximately 35000 in it. So what - i am testing here.

Well what should i say: It just broke because it needed more than 1G RAM...

I wonder why it seems like no one is yelling for awk? There are probably good books for it, too.

SCNR-Greetings

Everyone Has a First Day on the Job.

[LifesABeach]

If the dwellers of redmond wish to entertain us; I submit that an analysis of Nureal Nets using a simple spread sheet would be comfortable.

Comment removed

[account_deleted]

Comment removed based on user account deletion