Care to be taken while Joining 2 Files in IDEA

Primary tabs

1 post / 0 new
Brian Element's picture
Brian Element
Joined: 07/11/2012 - 19:57
Care to be taken while Joining 2 Files in IDEA

Hello Group Members,

We all use Join in IDEA routinely for the following indicative purposes -
A) To get fields from multiple files into a single file.
B) To match data across files.
C) To look for mismatches or unreconciled entries across files.
While matching two files based on a specific character field take care that the length of the field in both files is the same. If you try and execute the match with character fields of varying length you will get incorrect/incomplete results for sure.
Lets take a look at this scenario with a case study.
You have a mobile bill call data record listing delimited file converted to IDEA which contains Date, Time, Mobile Number Called (10 digit number), Duration, Units and Charge.
Your objective is to look at calls made from a company employee mobile number to blacklisted vendors.
You pick up the list of blacklisted vendors with their mobile numbers from an independent PDF file and while defining the layers, traps and fields using Report Reader you accidentally set the mobile number field length to say 7 when it should be 10.
Now both the files have the mobile number field but the former file field length is 10 but the latter is 7. This will pose a problem with the Join and give you incorrect results.
So when asked to set length for character fields using the Report Reader as an example (i.e. for PDF / Print Report / Report file conversion) always set the length correctly.
Needless to say this rule applies only for joins on character fields.