You can seen in this nice blog Context and Queue in Message Mapping UDF how you can use UDF (Used Defined Function) in conjunction with Queue to filter Employees based on department ID (to understand my complement please read it before).
To remind Source and Target structure have a look at Figure 1.
Figure 1
Scenario
- Question is what happens if Source system divides Employees for each department into two or more Department element like example in Figure 2, because Source structure allows it.
- And how to correctly create Target structure field elements i.e. ID, Name, Dep_ID, Dep_Name.
Figure 2
Node element Employee
We can handle the problem described in Scenario 1 with very small change in UDF code. UDF for example named getEmployee will show as we can see in figures 3 and 4.
Figure 3
Figure 4
The main change is that I inserted second loop into first loop in comparison to the code from the mentioned blog Context and Queue in Message Mapping UDF. UDF logic is almost the same as in that blog, but insertion second loop into first loop and initialization of value of variable store2 in each first loop means that check for matching (store1 == store2) - means Dep_ID[i] is equal to Dep_KeyID[0] - and adding values (Emp_ID[j]) into Target structure (node element Employee) are realised each time when condition (store1 == store2) occurs, not only for last occurrence.
In other words insertion of second loop into first loop means that for each Dep_ID[i] the function returns corresponding Emp_ID[j] if condition is true. Else this field is suppressed. And this is what we need for the Scenario 1 problem solution.
Second part of my blog will be about how we get values for another field (ID, Name, Dep_ID, Dep_Name) of Target structure, for the reason that direct mapping (e.g Emp_ID -> ID) don’t work correctly because we have to observe the Target structure as was created by UDF getEmployee for node element Employee – for our example see figure 5. We can do it again by using UDF in conjunction with Queue.
Figure 5
Field ID
For mapping field ID we create UDF named getEmpID, function detail we can see in figure 6 and 7.
Figure 6
Figure 7
This UDF has got three inputs:
- Dep_ID in context of message root.
- Emp_ID in context of Deparment.
- Dep_KeyID.
The main logic of UDF getEmpID is almost the same as logic of UDF getEmployee – two loops. In that field ID is normal field element not node element as element Employee, I added statement result.addContextChange() at two places to create same structure as was created for node element Employee by UDF getEmployee – see figure 8.
Figure 8
Whole mapping can be seen in figure 9.
Figure 9
Field Name
Mapping for field Name is the same as mapping for the field ID with one difference that we changed Emp_ID with Emp_Name in UDF.
Field Dep_ID
For mapping field Dep_ID we create UDF named getDepID, function detail we can see in figure 10 and 11.
Figure 10
Figure 11
This UDF getDepID has got the same three inputs as UDF getEmpID:
- Dep_ID in context of message root.
- Emp_ID in context of Deparment.
- Dep_KeyID.
Difference is in the code where function returns Dep_ID (result.addValue(Dep_ID[i])) for each Emp_ID[j] when conditions are fulfilled (store1 == store2) - means Dep_ID[i] is equal to Dep_KeyID[0] - else suppresses field. Please notice that function from second loop returns Dep_ID[i] where i is initialization and increment from first loop although second loop uses for initialization and increment j.
Field Dep_Name
For mapping field Dep_Name we create UDF named getDepName, function detail we can see in figure 12 and 13.
Figure 12
Figure 13
This UDF getDepName has got three four inputs:
- Dep_ID in context of message root.
- Emp_ID in context of Deparment.
- Dep_Name in context of message root.
- Dep_KeyID.
The function returns Dep_Name (result.addValue(Dep_Name[i])) for each Emp_ID[j] when conditions are fulfilled (store1 == store2) - means Dep_ID[i] is equal to Dep_KeyID[0] - else suppresses field. There is also true that second loop returns Dep_Name[i] where i is initialization and increment from first loop although second loop uses for initialization and increment j as in UDF getDepID.
Result
Result of mapping described here from example message in Figure 2 is message in next Figure number 14.
Figure 14
Conclusion
I try to add more knowledge and possibilities how we can use UDF in conjunction with Queue.
