Concatenating Fields within IDEA Vs MS-Excel

 

Every now and then we required to concatenate fields to create a single unified field for matching, grouping, profiling, gap detection and duplicate checking.

 

While IDEA allows users the benefit of the 'Power of the 8' which is up to an 8 independent field match or duplicate or summarization, at times we may need to apply concatenate fields in IDEA.

 

So this post looks at the method to concatenate in IDEA Vs MS-Excel.

 

In MS-Excel a concatenate is done by using =concatenate() through a new column insert in Excel.

 

In IDEA you can simply append a new virtual character field with the requisite length and simple apply the criteria as Field 1 + Field 2 where Field 1 and 2 are the independent character fields to be concatenated within IDEA.

 

Now in case you would like to place a space or a special character between the two fields simply use the criteria Field 1 + " " + Field 2 or you can use Field 1 + "~" + Field 2 to bring in a special character in between the fields.

 

An example to drive home the point as a case to apply Concatenate in IDEA would be ~

 

You require to identify missing Receipt Numbers across a Collections File. The catch is that the Receipt Number is generated as a running series for each Branch within the Collections File. Your requirement is to identify missing Receipt Numbers within each Branch and not across the entire File.

 

So while we acknowledge the need to use Gap Detection to meet our requirement, a straight jacket Gap Detection on the Receipt Number will be erroneous and misleading. So we can concatenate the Branch and Receipt Number into a single field using Branch + Receipt Number and then perform a Gap Detection on this concatenated field using the mask CCCCNNNNNN where Branch is a 4 digit code and Receipt Number is a 6 digit code.

 

Best Regards

 

Group Admin Team