How to Concatenate Two or More Fields within IDEA

Preparing data for the right kind of analysis is always of paramount significance within IDEA.

 

While IDEA allows you to use a combination of 8 group fields or matching fields or key fields within Summarization, Join and Duplicate Key Detection / Exclusion respectively, you may at times be required to just concatenate the fields together to achieve the desired result.

 

As an example ~ you have two fields in an Insurance Premium Collection data file within IDEA ~

 

Branch Code (which is like XYZ, ABC, TYP - Length of Character field is 3)

 

& Receipt Number (which is a running number series for each Branch independently like 123456, 123457, 123458 etc. - length of Character field is 4)

 

Now you need to look for missing Receipt Number's but Branch wise at one go through a single step in IDEA.

 

If you attempt to perform a Gap Detection on Receipt Number itself at one go, it will give erroneous results, as it will treat the running series of Receipt Numbers as one sequence for the whole Country rather than Branch wise which is the desired outcome for the test.

So here we need to concatenate the Branch and Receipt Number into a single field and then perform a Gap Detection for the concatenated field to get missing Receipts Branch wise.

 

So the concatenate can be done by appending a Virtual Character field titled 'CONCAT' having a length 9 with the criteria in the Equation Editor being -

 

 

This is the simple mechanism for a concatenate within IDEA and does not need any @Function like @concat etc.

 

The outcome will give to you in the Virtual Character field strings like ABC123456 or XYZ123513

 

Now just as a side topic for discussion, if you need to place a special character between the strings while executing the concatenate just tweak your criteria from above within the Equation Editor to ~ Branch Code + "-" + Receipt Number.

 

Your strings will now look like ABC-123456 or XYZ-123513

 

Best Regards

 

Group Admin Team