Method and system for aggregating tables having dissimilar formats

• US 5,272,628 A
• Filed: 04/16/1990
• Issued: 12/21/1993
• Est. Priority Date: 04/16/1990
• Status: Expired due to Term

• Current Assignee: Microsoft Technology Licensing LLC (Microsoft Corporation)
• Original Assignee: Microsoft Corporation
• Inventors: Koss, Michael C.
• Primary Examiner(s): Envall, Jr., Roy N.
• Assistant Examiner(s): Tran, Khai
• Application Number:
  US07/509,470
• Time in Patent Office: 1,345 Days
• Field of Search: 364/419, 364/523, 364/900, 364/518, 364/408, 364/200, 395/148, 395/161, 395/375, 395/146, 395/148, 395/156, 395/157
• US Class Current: 715/205
• CPC Class Codes: G06F 40/177 of tables; using ruled lines
• CPC Class Codes: G06F 40/18 of spreadsheets form-filling G06F40/174

Abstract

The present invention contemplates a method and system for automatically aggregating tables having a variety of configurations or layouts into a single destination table. Specifically, tables having a variety of categories with multiple divisions within those categories may be combined wherein rows and columns are automatically created in a destination table based on the categories and divisions of one or more source tables. In accordance with the teachings of the present invention, a plurality of source tables are selected as input to the system. A template containing categories and divisions for aggregation is then manually generated by a user or automatically generated by the computer system based on the categories and divisions contained within the source tables. Once the template is generated, mapping tables for rows and columns are created wherein each mapping table comprises an array of pairs of values wherein each pair comprises a first value for identifying a source table location and a second value for identifying a template location. The system then generates a single destination table by applying the mapping tables and by performing the desired mathematical function on the values in the source and destination tables (e.g., summing the values in the appropriate locations in the source and destination tables).

Claims

1. A method for aggregating tables in a computer system running a program, wherein the program uses tables to store and manipulate data, each table consisting of a grid of intersecting rows and columns, and where each column has a heading called a division and each row has a heading called a category, said method comprising the steps of:

(a) specifying a plurality of source tables for aggregation, each source table having at least one category and division;

(b) generating a destination table template, said destination table template specifying desired categories and divisions of said source tables to be aggregated into a destination table;

(c) creating a category mapping list that maps categories of said source tables to corresponding categories of said destination table template;

(d) creating a division mapping list that maps divisions of said source tables to corresponding divisions of said destination table template; and

(e) applying the category mapping list and the division mapping list to said destination table.

2. The method of claim 1 wherein said destination table template is manually generated by a user.

3. The method of claim 1 wherein said destination table template is automatically generated by the computer system.

4. A computer system for aggregating tables, the computer system running a program, the program using tables to store and manipulate data, each table consisting of a grid of intersecting rows and columns, each column having a heading called a division and each row having a heading called a category, the system comprising:

(a) input means for specifying a plurality of source tables for aggregation, each source table having at least one category and division;

(b) means for generating a destination table template, said destination table template specifying desired categories and divisions of said source tables to be aggregated into a destination table;

(c) means for creating a category mapping list that maps categories of said source tables to corresponding categories of said destination table template;

(d) means for creating a division mapping list that maps divisions of said source tables to corresponding divisions of said destination table template; and

(e) means for applying the category mapping list and the division mapping list to said destination table.

5. A computer system for aggregating tables, each table having an intersecting grid of rows and columns and each table having a format defined by the rows and columns in the table, the system comprising:

(a) a plurality of source tables for storing data, at least one of said source tables having a different format than other source tables;

(b) a destination table template for specifying the rows and columns from said source tables to be aggregated; and

(c) means for aggregating said source tables into a destination table, the destination table having the same format as the destination table template.

6. The system of claim 5 wherein the data stored in cells is numeric data.

7. A method for combining tables in a computer system running a program, wherein the program uses tables to store data, wherein each table contains a grid of intersecting columns and rows, wherein the intersection of each column and row is a cell capable of containing data, said method comprising the steps of:

specifying a plurality of source tables to be combined such that each source table column has a heading called a division and each source table row has a heading called a category and at least one source table has a different format than other source tables; and

generating a destination table, the destination table containing divisions and categories from the source tables in addition to data that is derived from data contained in cells form the source tables.

8. The method of claim 7 wherein the destination table contains the union of the divisions and categories of the source tables.

9. The method of claim 7 wherein the cells of the destination table contain data that are the result of a mathematical operation on the data from the corresponding cells in the source tables.

10. The method of claim 7 wherein the step of generating a destination table includes the additional steps of:

generating a destination table template containing selected divisions and categories from the source tables;

generating a mapping between the source tables and the destination table template; and

generating the destination table based on the mapping between the source tables and the destination table template.

11. The method of claim 10 wherein the destination table template contains user-selected divisions and categories.

12. The method of claim 10 wherein the destination table template contains divisions and categories automatically selected by the computer system.

13. The method of claim 10 wherein the step of generating a mapping includes the additional step of creating destination mapping tables that map the relationship of the source table categories to the destination table template categories and the source table divisions to the destination table template divisions.

14. A method for aggregating a plurality of disparate source tables into a destination table in a computer system running a program, wherein the program uses the plurality of disparate source tables to store and manipulate data, wherein each table contains a grid of intersecting columns and rows, wherein each column contains a column label and data, and each row contains a row label and data, the method comprising the steps of:

a) selecting the plurality of source tables, each source table comprising at least one column label and at least one row label;

b) determining which column and row labels from the plurality of source tables will be represented within the destination table; and

c) for each source table,

creating a column mapping list, the mapping list containing a list of paired entries (s_c,d_c), each s_c representing a source table column label and each d_c representing a destination table column label such that the data in the represented source table column will be stored in the represented destination table column,

creating a row mapping list, the row mapping list containing a list of paired entries (s_r,d_r), each s_r representing a source table row label and each d_r representing a destination table row label such that the data in the represented source table row will be stored in the represented destination table row, and

adding data from the source table to the destination table in accordance with the column and row mapping lists, the column and row mapping lists supplying the correct destination column d_c and destination row d_r for storing data contained within a source table column s_c and source table row s_r.

15. The method of claim 14 wherein the step of determining which column and row labels from the plurality of source tables will be represented within the destination table includes receiving input from a user of the computer system regarding desired column and row labels.

16. The method of claim 14 wherein the step of determining which column and row labels from the plurality of source tables will be represented within the destination table includes the step of including a row label in the destination table for each unique row label in the plurality of source tables and including a column label in the destination table for each unique column label in the plurality of source tables.

17. The method of claim 14 wherein the step of adding data from the source table to the destination table includes the steps of:

for each paired entry (s_c, d_c) in the column mapping list,

for each paired entry (s_r,d_r) in the row mapping list, adding the data stored at the intersection of the row containing row label s_r and the column containing column label s_c in the source table to the data stored at the intersection of the row containing row label d_r and the column containing column label d_c in the destination table.