On occasion, an IDEA user may be provided data that doesn’t necessarily contain the information
needed for certain types of reporting.
For example, the provided data may contain multiple lines for a specific document number (DOCNO) when it is instead preferable to include a line-item number against each line that has the same document number (LINENO). Ideally, this information would be generated by the system the data was extracted from, but this is not always the case. Fortunately, IDEA contains functionality that can enable users to add this information into their data.
Creating Line Numbers
Open the IDEA database that you want to add the line-item number to.
In the example below, you can see that although the document number repeats multiple times. For this process to work, all of the records that you want to group together need to be vertically sorted together. To achieve this, you will need to use the Sort task on the Data tab in IDEA.
The next step is to then give each record in your sorted IDEA database a unique number. This will be needed later in the process when calculating the line-item number.
To create this field Append a new Numeric field (not Virtual or Editable) called RECNO using either Field Manipulation or the Append Field (v10) or Add Field (v11) options on the data cell, right-click Menu and the @Precno() formula to create this unique record number.
The next step is to add a field that will identify the first line for each unique group that you defined when sorting your data.
@If(@GetPreviousValue(“DOCNO”) <> DOCNO, “Y”,””)
You now need to create an IDEA database which only holds the first lines records. This can be achieved by using the Direct Extraction function on the Analysis toolbar
FIRST_LINE == “Y”
For each document number, you will need to include on each record the unique number that the first line for that category belongs too. In this example, this is based on the DOCNO field.
Open the FIRST LINES ONLY database and rename the RECNO field to FIRSTLINE_RECNO.
Open the sorted database created in Step 1 and make this the Primary and active database. Join this file to the FIRST LINES ONLY database.
Include all fields, except for FIRST_LINE, from the Primary database and include only the FIRSTLINE_RECNO field from the Secondary database. Match on the field or fields that you originally sorted your data on:
The final stage will be to create the document number related line number. This can be achieved by appending a new Virtual Numeric field called LINENO.
(RECNO – FIRSTLINE_RECNO) + 1
As per the example above your database will now include the line item.
Creating a Transposed File
Now that we have created this line number, one way that we can make use of it is to create a transposed report where all of the amounts relating to the same document number are recorded on one line.
Open the Payment Data – Sorted with 1st Line No database (see Step 5) and select the Pivot Table option on the Analysis tab. Enter a name for the pivot table – this will also be the name of the database created at the end of this process. Then:
1. Add DOCNO to Drop Row Fields Here
2. Add LINENO to Drop Column Fields Here
3. Add AMOUNT to Drop Data Item Here
4. Finally, click on the tiny drop-down icon
5. Make sure Send to IDEA Database is checked – if it isn’t, click on it to select it.
6. With Send to IDEA Database checked, click on the icon itself.
This will create the final result database: