Many times we have seen a requirement of getting the count of a specific node or field that needs to be populated to a field in the trailer or say any field in the target.
In this wiki post, I will try to document the easiest way for you to get around that requirement. Let first take a very simple scenario;
The Source and Target message in this case is going to remain the say, its going to be a 1-1 mapping except the fact that we need a COUNT to be populated to a field in the target Trailer.
The Structure of the Message is as below;
Let for the time being have the same message in the target.
1. The requirement is to populate the count of the Recs node in the TrailerCount Field i.e Trailer count should have exact number of occurrence of the Recs node
Below is the mapping;
The logic for the count is very simple;
Use the standard Statistic COUNT function.
2. When we have more record fields and we need the total count of all the different record fields??
The Message structure is
Solution: Same Logic
Use add and count in combination.
Reference as below;
Now lets get into some complex conditions that you might have to handle on a requirement basis.
Let say if the mapping logic provided to you by the business is as below;
a. Only Map ALL Source Recs_1 fields to Target Recs_1 field where CITY is “GOTHAM CITY”
b. Only Map ALL Source Recs_2 fields to Target Recs_2 field where Region is “INTERGALACTIC”
c. Populate the count of the TARGET Recs_1 and Recs_2 field to TARGET -> TRAILER -> TrailerCount
So ideally you will do the mapping as said in the above. Of course a and b are daily mapping routine so lets come to c
OPTION 1. If you ask me what is the easiest way then my suggestion is create one more mapping (say MAPPING_2) where you will map the target structure to the target structure. A pure easy 1-1 mapping. So now in the interface mapping you will add this MAPPING_2 after the original mapping (say MAPPING_1). In the first mapping you will just ignore the mapping for TrailerCount but take care of all required mapping logic and in the second mapping i.e MAPPING_2 you will use the count function with sum to get the count.
Both the logic i.e a and b will taken care here. Leave the mapping for TrailerCount
For Recs_1 node
For Recs_2 node
For the employee;
Similarly as above the mapping for other fields will also be done.
Now create another mapping say MAPPING_2
It will all remain a 1-1 field mapping but the we will take care of the count here as we have done in earlier case using the count function.
So the result of MAPPING_1 is only the records as we needed per mapping logic and now second mapping will only have the right record and so easily we can apply the count.
Result of Mapping 1:
In interface mapping have the MAPPING_1 first followed by MAPPING_2
During testing the Interface mapping, the result is;
So there you go…. you got the result
OPTION2 : Handle the count in the first mapping itself, so that means only one mapping is required.
Do all the node and field mapping as per the mapping logic. Now coming to the TrailerCount you will need to use the same logic as for the NODE creation of Recs_1 and Recs_2
So your mapping will be;
Note: Option 2 is preferable in case you have a simple structure. But in case you have a complex structure, I suggest you use the Option 1