JPH04181372A - Table sheet totalization system for data - Google Patents

Abstract

PURPOSE:To set totalization conditions in a condition file and generate a necessary total table by providing a totalizing process means which can set the totalization conditions variably through data on the totalization conditions stored in the condition file. CONSTITUTION:This system has the condition file 20 which contains data on the name of a data file to be totalized, record items to be totalized, key items and ranges of classification or extraction, an arithmetic method when arithmetic such as rate calculation other than totalization is needed, and the totalization conditions such as the array order of respective items in the totalization table and a totalizing process program which generates totalization tables differing in totalization conditions from a data file 40. In this case, the totalizing process means is set by using the totalization conditions as parameters. Consequently, the different totalization conditions are stored in the condition file to generate the different totalization tables by the one totalizing processing means.

Description

DETAILED DESCRIPTION OF THE INVENTION [Field of Industrial Application] The present invention relates to a data aggregation method in an office computer.

[Prior Art] In general, office computers perform periodic and regular data aggregation work from a large amount of data files that are constantly updated, as well as temporary and non-routine data collection as management and planning materials. A summary table of recent data is often required. All of these tally tables are output in a tabular format in which numerical values tallied at multiple stages such as subtotals and width totals are arranged vertically and horizontally for various classification items.

Conventionally, in the case of programs that are used repeatedly on a regular basis, the programs for performing these aggregations are created in COB or other general-purpose languages by experienced personnel after sufficient system design for each job. In the event of an unexpected or temporary situation, a person in charge or the like would create a program in a simple language such as an RPG or a personal computer spreadsheet, and then tally the data.

[Question B to be solved by the invention] The conventional aggregation programs mentioned above are created individually for each job with fixed classification items and output formats determined for each job, and the classification methods, display formats, etc. Existing block diagrams cannot be used for different aggregation tasks, even if they are aggregations from the same data file, so in order to obtain a summary table with different aggregation conditions from the existing program, a completely new program must be created. However, there is a drawback that there is a risk of losing time in response to the latest data collection4 or urgent requests. In addition, when using a simple language, it is relatively easy to create a program, but there are many restrictions on the classification of large I data, the number of stages of totals such as extraction, subtotals, width totals, etc., so the required aggregation work The disadvantage is that it is often not possible to respond to

An object of the present invention is to provide a tabular tabulation method for data that can create a necessary tabulation table by setting tabulation conditions in a condition file.

[Means for Solving the Problems J] The tabular data aggregation method of the present invention includes data file names to be aggregated, record rings [] to be aggregated, key items and their ranges for segmentation extraction, ratios other than aggregation. A condition file that stores data representing aggregation conditions such as the calculation method when there is an operation such as calculation, and the order of arrangement of each item on the summary table, and the data stored in the condition file from the data file. As a parameter, it has an aggregation processing program that creates a summary table with different aggregation conditions.

[Operation] Since the aggregation processing means is set with aggregation conditions as parameters, different aggregation tables can be created by one aggregation processing means by storing different aggregation conditions in the condition file.

[Example] Next, an example of the present invention will be described with reference to the drawings.

FIG. 1 is a configuration diagram of an embodiment of the data tabulation method of the present invention, FIG. 2 is a diagram showing the relationship between each process and file of the cross collection H10, and FIG. 3 is the parameter setting of the condition file 20. FIG. 4 is a flowchart of aggregation processing 4, and FIG. 5 is a diagram showing an example of numerical values.

As shown in FIGS. 1 and 2, the configuration of this embodiment includes a data file 40 consisting of a large number of data files in which various data are accumulated and updated at any time or regularly; Stored data items/
An environment file 30 consisting of files and tables storing environmental information of data in a data file 40 such as a definition information file of attributes and record configurations and a correspondence table of codes and names, and a data extraction range for each aggregation task;
A condition file 20 that stores data on aggregation conditions such as key items for various classifications and designation of placement of output data in a summary table, an aggregation result file 50 that stores aggregation results, and aggregation that is set for each aggregation job. Parameter setting 13 stores the conditions in the condition file 2o, extracts, categorizes, and aggregates data from the data file 40 by referring to data in the set condition file 20 and environment file 30, and stores the aggregated data in an aggregated result file. Aggregation processing 14 to be stored in 50
and ancillary processing 15 that performs processing such as copying and deleting the data of the set condition file 20.
Consists of 0.

Next, an outline of the cross-tabulation processing 10 will be explained with reference to FIGS. 2 and 3.

First, a data file to be aggregated is selected from among the data files 40, and the system is called up by the console (data file selection 11).

“Parameter setting 13” is displayed on the monitor screen 101A of the operation console.
”, “Aggregation processing 14”, “Additional processing 15”, or “Processing end”, the name of the existing summary table will be displayed along with the question of which function to select (Title input/Process selection 12, Step 101).

When setting new aggregation conditions and monitor screen 101A
"Parameter setting 13" is specified when modifying or changing the existing summary table displayed in , and a new title name is registered when new settings are made. Name is specified. If a coat conversion is necessary, the conversion is performed on the monitor screen 102A (step 10).
2) If there is no need for code conversion, skip the process and move on to the next process. Setting conditions for further extracting data in the aggregation target range from the specified data file 40 (step 103) is performed as shown in monitor screen 1.03A.
The key item data for extraction is the "range" from "A" to rBJ, or rcJ, rD,... rVJ
Up to 20 values are specified individually in the form of ``include'' or ``exclude,'' and the key items are set by connecting up to 9 key items with an AND (step 103). The vertical aggregation division of the summary table, that is, the total division, is “Individual”, “
It is possible to set up to three key items such as ``subtotal'' and ``medium needle'' (step 104). Setting horizontal aggregation conditions is divided into three steps: ``Horizontal aggregation item settings (Step 105)'', ``Horizontal aggregation condition settings (Step 106)'', and ``Horizontal item calculation method (Step 107)''. ``Setting horizontal summary items (step 105) J'' sets the item number of the data to be output and the output name as the heading of the item in the output frame in the order of arrangement in the 20 divisions arranged horizontally. By specifying the horizontal item, the horizontal item is specified (step 105).
. As shown in the monitor screen 106A, the aggregation conditions for each of the horizontal aggregation items that have been set are as follows: You can connect up to 3 pairs of or's to create one set, which can then be set by connecting up to 3 pairs of or's (step 106).
, Also, as a horizontal summary item, rather than just aggregation, %
Items that display calculation results such as ratios can also be set. Setting calculation methods other than such aggregation is "
Setting the horizontal item calculation method (step 107)
” to “Value 3” column, enter the data item number or constant to be operated on, and add, subtract, multiply, and divide the symbols (+, -, *) in the “OP” column between them.
, /), respectively.
The calculation result is stored in the frame specified in the frame No. J column (step +07).

Data such as items and calculation formulas set by the above processing are stored in the condition file 2o, and when the processing of "parameter setting 3" is completed, the process returns to "processing selection 2" again.

When "Tally process 14" is specified, as shown in FIG. 4, the tally program 140 refers to the data stored in the condition file 20 and the environment file 3o,
Extract and classify data to be aggregated from the specified data file 40 (step 141), perform aggregation for each aggregate item and perform calculations specified as the horizontal item calculation method (
Step 142), edit the calculation results such as arranging them in the order specified in the output frame, converting the title and other codes from tables to character expressions (Step 143),
Appear in the total result file 5o (step 144),
When the aggregation process 14 is completed, the process returns to the process selection 12.

"Ancillary processing 15" is ancillary processing accompanying parameter setting 13 and aggregation processing 14, such as copying and deleting each file.
The processing method is the same as general file copying, file deletion, etc.

Next, the parameter setting 13 will be specifically explained using a numerical example.

FIG. 5 is a diagram showing the relationship between the input data, reference file, and summary table of output results in this numerical example.

This numerical example is based on the sales input file A, which is one file in the data file 40 (data such as the number of sales by store, the number of sales by item, and the selling price per item are stored in file A every day as one record. ) to each item (
Aggregation of sales amounts for the first quarter (April 1st to June 30th) categorized by district blocks such as prefectures and business formats of directly managed stores and agencies (franchise stores), and by district. The purpose is to obtain an output summary table including the sales ratio and the sales ratio of item B-2 (alcoholic beverages).

Sales cuff 7-i' AJA (FILE HD=U
RIIO1), item table C()IINMO2
The record format of all data, including various tables and files in the environment file 30 such as ), dealer table B (,)iANBO3), is in the environment file 3.
It is stored in the record definition file in the environment file 30 in the format shown in Table 1, and the cited item numbers in Table 1 are common to all data in this system.
It is stored in the item attribute file in the format shown in Table 2.

Table 1 Table 2 First, "sales input file A" is selected from the data files 40, and the monitor screen displays screen 101A in FIG.
is displayed (data file selection 11 in Figure 2). Next, "New Parameter Settings 13" is selected, and the title "
The words "Sales table by item/by district block/1st year IQ period" are input as the title (step 101).

Skip step 102 as "coat conversion" is unnecessary and move on to "extraction condition setting (step 103)." Since "extraction condition" is all data for the first quarter in sales input file A, "item number The data of tlRO3J is r
8904 old” or more r “range j” up to 890630J
Specify that "there is" within. If you want to exclude specific days such as sale days, you can specify "exclude" by "listing" the respective year, month, and day data next to "and" (screen 103A).

The next vertical aggregation division is by prefectural district block and by business type of direct management/agency, so the fourth item (
4th item () IAO4) and 7th item () of dealer table B referenced from the dealer code of URO4)
lAO7) are specified in the item number columns of the subtotal and medium-term plan, respectively, and furthermore, the item "district court" of the medium-term plan is specified to mask the last two digits of the municipal court (screen 104A).

When setting horizontal summary items (step 105), up to 20 frames can be specified from the first frame on the left (frame No. 1001) to the right, so the horizontal item headings are rA-1'', rA-2.
Specify the output name such as J and the data item to be aggregated for each output frame. In this numerical example, the aggregation target is "sales amount" and there is no data that directly displays "sales amount" in "sales input file A", so "sales input file A" is directly entered in the data item field for aggregation target. cannot specify the item number. In such a case, specify a work frame (frame number bones WO21 to WO40) different from the output frame and set the unit price (
URO8) It is sufficient to store the processing result of x quantity (tlRll) and specify the work frame number (for example, WO21) in the tally item number column of the output frame (screen 105A). In the next "Horizontal aggregation condition setting (step 106) J, the category and range of the aggregate data are set for each aggregate item (WO21, etc.) of the set output frame. For example, output frame r1001J,
Output name rA-1. J, summary item N.

rWO21J is "Item classification of sales input file AJ (
lJRO5) as the classification key, monotosleva representing animal food products from rlollll F beef)' to rl, 02990 (other marine products)' ([IRO5)
(!, 01111) or more (+02990), and (1026xx seaweeds) and (1027xx
(excluding processed seaweeds). Other output frames (10
02 to +020) in the same way, specify the aggregation range for the item number that is the classification key (Screen 1
06A). Among the horizontal summary items, if calculations other than simple addition such as unit price x quantity calculation or percentage ratio are required, set them in the horizontal item calculation method setting (step 107). . For example, output frame 1019
The column ``R area ratio'' has a total value of 1 (output frame N.

1018), value 2 is the grand total (GO18), value 3 is the constant (
1,00), and the division symbol (
1) If you enter a multiplication sign (*) in the "○P" field between value 2 and value 3, (output frame number, 1018) ÷ (GOI8)
It shows that the calculation result of x (100) is stored in output frame number 1019 (screen 107A).

The parameters set by the above-mentioned processing are stored in each file of the condition file 20, and the parameters set by the above processing are stored in each file of the condition file 20, and are
4 is specified, the aggregation program shown in FIG. 4 reads the necessary data from the data file 40, environment file 30, and condition file 20 and performs aggregation processing.
An output summary table is created and stored in the summary result file 5o. After that, it can be used by printing out a tally list on printer paper or calling it up on a monitor screen for search processing.

[Effects of the Invention] As explained above, the present invention provides an aggregation processing means that can variably set aggregation conditions through the aggregation condition data stored in a condition file, so that aggregation conditions can be stored in a condition file. This has the effect that a specified summary table can be easily created by the totalization processing means without creating a new program.

[Brief explanation of the drawing]

FIG. 1 is a configuration diagram of an embodiment of the data tabulation method of the present invention, FIG. 2 is a diagram showing each process of the cross tabulation 10 and the relationship between files, and FIG. 3 is the parameter setting of the condition file 20. 13 flowchart and a display example of the monitor screen at each step, FIG.
The flowchart in FIG. 5 is a diagram showing a numerical example of parameter setting 13. 10...Cross tabulation processing, 11...Data file selection, 12...Title input/process selection, 13...Parameter setting, 14...Tally processing, 15...Additional processing, 20...・Condition file, 30...Environment file, 40...Data file, 50...Tally result file, 101-107.141-144...Step, 10
1A-107A...Monitor screen display example, A...Sales input file, B...Sales store table, C...Item table, D...Output summary table.

Claims (1)

[Claims] 1. A tabular aggregation method for data accumulated in data files, which includes data file names to be aggregated, record items to be aggregated, key items for classification/extraction and their ranges, and information other than aggregation. A condition file that stores data representing aggregation conditions such as the calculation method when there is a calculation such as a ratio calculation, and the order of arrangement of each item on a summary table; A data tabulation method that includes a tabulation processing program that uses data as parameters to create a tabulation table with different aggregation conditions.