IDEA Advanced, IDEA TECH TIPS

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

Step 1

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.

Default document order
The Sort database task in IDEA's Data tab
The sorted document order

Step 2

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.

Field Manipulation dialog window
The new RECNO column

Step 3

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.

Field Manipulation dialog window

@If(@GetPreviousValue(“DOCNO”) <> DOCNO, “Y”,””)

The formula output

Step 4

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

Direct Extraction dialog window

FIRST_LINE == “Y”

The formula output

Step 5

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.

FIRST LINES ONLY database

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 full Join database process

Step 6

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.

Field Manipulation dialog window

(RECNO – FIRSTLINE_RECNO) + 1

The final output

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.

Step 1

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:

IDEA Advanced badge

Leave a Reply

Your email address will not be published. Required fields are marked *