Analysis made Light - Feature in MS-Excel made easier in IDEA

Brian Element

Forums

Hi,

 

We often come across situations where we need to reconcile two databases where the relationship between the transactions in both databases is 'one to many' or 'many to one' or 'many to many' - as an example - a single purchase order having multiple line order items in one database and the same purchase order having multiple goods received in another database.

 

In such a situation the standard approach in MS-Excel would be the following -

 

a. Apply a pivot table on database 1

 

b. Apply a pivot table on database 2

 

c. V-Lookup the pivot from database 1 and 2

 

d. In the V-lookup file add a column for difference in order quantity and goods received quantity

 

While the above is done with great speed and comfort by most Excel aficionados it would slow down the excel process with each step mentioned from a. to d. on larger excel files. Added to the performance issue is the underlying risk of data integrity on excel worksheets and non-availability of an audit trail to log every step in Excel.

 

On the other hand IDEA has a single function titled 'Compare' which performs a Summarization, Join and Field Manipulation in a single step.

 

So while we use Excel to perform day to day analytics it is prudent to graduate to IDEA for faster and convenient processing of data.

 

Kind Regards

 

Jairam

 

Brian Element Tue, 06/09/2015 - 06:15

Hi,

We often come across situations where we need to draw up a report representing top or bottom values in a database - as an example - in a general ledger we would like to identify the top 10 debits and credits independently per head of account for sampling purposes for the last quarter of the financial year and location 'A'.
 
In such a situation the standard approach in MS-Excel would be the following -
 
a. Apply a filter for last quarter of the financial year and Location 'A'
 
b. Copy the filtered data from a. above to another work-sheet and sort the data on head of account ascending and amount descending.
 
c. Then group the sorted data into head of accounts and manually extract the top 10 positive values and then the top 10 negative values into a separate work-sheet.
 
The steps given in a. to c. above are cumbersome and time consuming on large excel files. Added to the performance issue is the underlying risk of data integrity on excel worksheets and non-availability of an audit trail to log every step in Excel.
 
On the other hand IDEA has a single function titled 'Top Records Extraction' which performs a Filter, Group and Sort in a single step.

Brian Element Wed, 06/10/2015 - 06:46

Hi,

When I view the Direct Extraction in IDEA it represents a carriage drawn by 50 horses metaphorically.
 
In the metaphor the carriage is the database imported into IDEA and the 50 horses represent 50 independent extractions which are possible through a single pass of the database.
 
The Direct Extraction in IDEA has the distinction of allowing the user to build up to 50 independent criteria/conditions/parameters on the active database. The 'piece de resistance' is that all the extractions will run simultaneously on the parent active database and create 50 independent reports.
 
Not only is it effective it is immensely economical.
 
So unlike MS-Excel I need not wait to apply a criteria copy the output view to a new worksheet, remove the criteria and apply a new criteria again.

Brian Element Fri, 06/12/2015 - 06:42

Hi,

 

The Visual Connector in IDEA is yet another superlative feature where IDEA raises the bar on spreadsheet based data analytics.

 

Picture a situation where you would like to trend the average net price product wise across a period of 12 months. This would require the user to apply multiple V-Lookup's in Excel to arrive at a single worksheet having the trend table for 12 months. Seems possible, but it is fraught with deteriorating spreadsheet function response time and the obvious risk of data integrity while performing multiple V-Lookup's.

 

On the positive side, IDEA has the Visual Connector which can link the 12 month sales files in a single jiffy and dialog screen. What's more the linking of monthly tables is a simple drag and link of the matching key fields between the files. Further, the user can specific the field to compare i.e. sales net price from each monthly file by a simple tick on the required field. Visual / Pictorial matching adds tremendous ease to the multi-file join process and is very intuitive too.

 

We've seen IDEA execute a Visual Connector on large databases in a few seconds to a few minutes. So its both quick and extremely effective.

 

I've seen teams use the Visual Connector effectively for trend analysis and statutory compliances and each time the Visual Connector comes out as the Hero.

 

Kind Regards

 

Jairam

 

dhuffman63 Thu, 04/23/2020 - 10:39

I'm reading this and trying to figure out how to use this to do what I'm doing in Excel in IDEA.  I have 2 databases, Employee Addresses and the Vendor Address Master.  Out of those two I'm taking the Address in col 1 and the City and concatenating them into AddressCity.  I have Empy Address on one tab and the Vendor address on tab 2.  I then use vlookup in tab 1 to compare (match?) to the vendor address in tab 2 to determine Unique or Duplicate.
Basically I'm looking for Employee addresses that match the Vendor Master Addresses.  Is there an easier way to do this in IDEA?