Analysis made Light - Feature in MS-Excel made easier in IDEA
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
Hi,
Hi,
Hi,
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
I'm reading this and trying
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?
Sounds like you want to do a
Sounds like you want to do a fuzzy match, here is a video on how to do it manually - https://www.youtube.com/watch?v=Lu3mwVqE-G4&list=PLEE1l8LoXUCLS2GYi5QsvNuPuRoez3L2v&index=21
If you have IDEA 11.1 go into the IDEA lab and there is a plug in called Fuzzy Join that will also do this.
Hi,
Hi,