Care to be applied while Joining 2 Files in IDEA

Let us say you wish to match (Join) two files in IDEA - the first - a Transaction file and the second - a Master file to identify unreconciled items/mismatches/short charges/excess levies and more. 

Now if both the files are generated from the core application system of the Auditee then your matching key field/s in the file/s would largely have the same name and field type making the Join process seamless.

However let us say the Master file is maintained outside the core application system manually. We come across this situation umpteen times.

While executing the Join between the Transaction file from the core application system and the Master file from the manually maintained records, your common key field while available in both files, does not have the same field type. Let us say it is Character in the Transaction file and Numeric in the Master file.

Now we are aware the Join will throw up an error owing to mismatch in field types. So a standard resolution process we follow is to go to Field Manipulation in the Master file and change the matching key field type from Numeric to Character and then proceed with the Join.

This is the moment care needs to be applied. If the matching key field in the Transaction file is Character with say length 9 digits and the matching key field in the Master file is Numeric converted to Character with say length 8 digits, the Join will go through, but give erroneous results and incomplete matches.

So the care we need to take while making the conversion from Numeric to Character in the Master file is make sure the field length is also set to 10 (matching the field length of the Transaction file).

This activity may appear insignificant and trivial in the grand scheme of analysis. But can have significant repercussions on your results.

Regards

 

Group Admin Team