on 05-23-2014 1:20 PM
Hi PI buddies and gurus.
I have a problem regarding a sorting requirement. Upon trying out all the basic functions in PI, I can confirm that it cannot be done without using a UDF. Honestly, I'm not that proficient in JAVA. I would like to ask for your help guys. Kindly see source and IDEAL output below:
case (parent node)
caseid 1111 (field)
deliveryid (subnode)
deliverynumber 1 (field)
deliveryid
deliverynumber 2
case
caseid 2222
deliveryid
deliverynumber 1
deliveryid
deliverynumber 1
case
caseid 3333
deliveryid
deliverynumber 1
case
caseid 4444
deliveryid
deliverynumber 1
deliveryid
deliverynumber 2
So the requirement is to separate the deliverynumbers into multiple idocs. in the sample above, we will only have 2 idocs because of deliverynumbers 1 and 2. the next requirement is to group the deliverynumbers according to their case. the problem with the standard functions sort and sortbykey is that they don't take into account the splitting of the deliverynumbers into two idocs. Kindly see below output for better understanding of the issue.
output (ideal):
idoc
header
case
caseid 1111
delivery
deliverynumber 1
case
caseid 2222
delivery
deliverynumber 1
delivery
deliverynumber 1
case
caseid 3333
delivery
deliverynumber 1
case
caseid 4444
delivery
deliverynumber 1
idoc
header
case
caseid 1111
delivery
deliverynumber 2
case
caseid 4444
delivery
deliverynumber 2
If we use the normal sorting functions of PI, and use case as reference for the case segment in the output, we will only have 4 contexts (1111 2222 3333 4444). And if we use the deliverynumber as reference for the case segment in the output, we will have 7 instances. As you may have noticed in the ideal output, there are only 6 instances of the case segment. Also, the case segments were separated according to the deliverynumber value.
Is this at all possible using UDF? I'm having a hard time generating the necessary codes. Please help.
Hi,
Harish's suggestion may be a little easier to manage with the grouping/sorting aspects but I was able to do it in one map by mapping in this fashion and with three additional UDFs. For this example I ignored the 'header' node of the XML because it didn't appear to contain any data in your example. The field deliverynumber was used at the case context for all node mappings except 'idoc' and 'deliverynumber' See the screenshots and code below:
idoc:
case:
caseid:
deliveryid:
deliverynumber:
removeDuplicates UDF:
String tmp = "";
for(int i = 0; i < input.length; i++)
{
if(tmp.compareTo(input[i]) != 0)
{
result.addValue(input[i]);
tmp = input[i];
}
}
splitCaseByDelivery UDF:
String tmp = "";
ArrayList<String> al = new ArrayList<String>();
for(int i = 0; i < input.length; i++)
{
if(tmp.compareTo(input[i]) != 0)
{
result.addValue(input[i]);
tmp = input[i];
}
else
{
al.add(input[i]);
}
}
if(al.size() > 0)
{
splitCaseByDelivery(al.toArray(new String[0]), result, container);
}
splitDelivery UDF:
if(input.length == 0)
return;
String tmp = input[0];
ArrayList<String> al = new ArrayList<String>();
int i = 0;
while(i < input.length)
{
if(tmp.compareTo(input[i]) == 0)
{
result.addValue(input[i]);
for(int j = i+1; j < input.length; j++)
{
result.addValue(input[j]);
if(input[j].compareTo(ResultList.CC) == 0)
{
i = j;
break;
}
}
}
else
{
al.add(input[i]);
for(int j = i+1; j < input.length; j++)
{
al.add(input[j]);
if(input[j].compareTo(ResultList.CC) == 0)
{
i = j;
break;
}
}
}
i++;
}
if(al.size() > 0)
{
splitDelivery(al.toArray(new String[0]), result, container);
}
The UDFs for case id remove duplicate delivery so you'll get the right number of case ids and then it shifts additional occurrences of the same case id using recursion to the next idoc contexts when it must be created more than once. The UDF for deliverynumber does something similar where all the same numbers will be reorganized into the same idoc context but in the proper order.
Regards,
Ryan Crosby
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
hi ryan,
everything's working fine except for the SplitDelivery UDF it worked in the sample above because there's only 2 deliverynumbers. if there are 3 or more, the data get mixed up
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
Hi again Ryan 1 test file failed The Delivery segments for the 2nd idoc were misplaced in the 1st idoc.
i'm not sure though how to attach a sample file here.
<?xml version="1.0"?>
<Document>
<Order>
<OrderID>1111</OrderID>
<case>
<caseid>1111</caseid>
<deliveryid>
<deliverynumber>2</deliverynumber>
</deliveryid>
<deliveryid>
<deliverynumber>2</deliverynumber>
</deliveryid>
<deliveryid>
<deliverynumber>2</deliverynumber>
</deliveryid>
<deliveryid>
<deliverynumber>2</deliverynumber>
</deliveryid>
<deliveryid>
<deliverynumber>2</deliverynumber>
</deliveryid>
<deliveryid>
<deliverynumber>2</deliverynumber>
</deliveryid>
<deliveryid>
<deliverynumber>2</deliverynumber>
</deliveryid>
<deliveryid>
<deliverynumber>2</deliverynumber>
</deliveryid>
<deliveryid>
<deliverynumber>1</deliverynumber>
</deliveryid>
<deliveryid>
<deliverynumber>2</deliverynumber>
</deliveryid>
<deliveryid>
<deliverynumber>2</deliverynumber>
</deliveryid>
<deliveryid>
<deliverynumber>1</deliverynumber>
</deliveryid>
<deliveryid>
<deliverynumber>1</deliverynumber>
</deliveryid>
<deliveryid>
<deliverynumber>1</deliverynumber>
</deliveryid>
<deliveryid>
<deliverynumber>1</deliverynumber>
</deliveryid>
<deliveryid>
<deliverynumber>1</deliverynumber>
</deliveryid>
<deliveryid>
<deliverynumber>2</deliverynumber>
</deliveryid>
<deliveryid>
<deliverynumber>1</deliverynumber>
</deliveryid>
<deliveryid>
<deliverynumber>1</deliverynumber>
</deliveryid>
<deliveryid>
<deliverynumber>1</deliverynumber>
</deliveryid>
<deliveryid>
<deliverynumber>1</deliverynumber>
</deliveryid>
<deliveryid>
<deliverynumber>1</deliverynumber>
</deliveryid>
</case>
<case>
<caseid>2222</caseid>
<deliveryid>
<deliverynumber>1</deliverynumber>
</deliveryid>
<deliveryid>
<deliverynumber>1</deliverynumber>
</deliveryid>
<deliveryid>
<deliverynumber>1</deliverynumber>
</deliveryid>
<deliveryid>
<deliverynumber>1</deliverynumber>
</deliveryid>
</case>
</Bill>
</Document>
what's weird though is that if i change the last deliverynumber to 2, it will work fine
correct source:
<?xml version="1.0"?>
<Document>
<Order>
<OrderID>1111</OrderID>
<case>
<caseid>1111</caseid>
<deliveryid>
<deliverynumber>2</deliverynumber>
</deliveryid>
<deliveryid>
<deliverynumber>2</deliverynumber>
</deliveryid>
<deliveryid>
<deliverynumber>2</deliverynumber>
</deliveryid>
<deliveryid>
<deliverynumber>2</deliverynumber>
</deliveryid>
<deliveryid>
<deliverynumber>2</deliverynumber>
</deliveryid>
<deliveryid>
<deliverynumber>2</deliverynumber>
</deliveryid>
<deliveryid>
<deliverynumber>2</deliverynumber>
</deliveryid>
<deliveryid>
<deliverynumber>2</deliverynumber>
</deliveryid>
<deliveryid>
<deliverynumber>1</deliverynumber>
</deliveryid>
<deliveryid>
<deliverynumber>2</deliverynumber>
</deliveryid>
<deliveryid>
<deliverynumber>2</deliverynumber>
</deliveryid>
<deliveryid>
<deliverynumber>1</deliverynumber>
</deliveryid>
<deliveryid>
<deliverynumber>1</deliverynumber>
</deliveryid>
<deliveryid>
<deliverynumber>1</deliverynumber>
</deliveryid>
<deliveryid>
<deliverynumber>1</deliverynumber>
</deliveryid>
<deliveryid>
<deliverynumber>1</deliverynumber>
</deliveryid>
<deliveryid>
<deliverynumber>2</deliverynumber>
</deliveryid>
<deliveryid>
<deliverynumber>1</deliverynumber>
</deliveryid>
<deliveryid>
<deliverynumber>1</deliverynumber>
</deliveryid>
<deliveryid>
<deliverynumber>1</deliverynumber>
</deliveryid>
<deliveryid>
<deliverynumber>1</deliverynumber>
</deliveryid>
<deliveryid>
<deliverynumber>1</deliverynumber>
</deliveryid>
</case>
<case>
<caseid>2222</caseid>
<deliveryid>
<deliverynumber>1</deliverynumber>
</deliveryid>
<deliveryid>
<deliverynumber>1</deliverynumber>
</deliveryid>
<deliveryid>
<deliverynumber>1</deliverynumber>
</deliveryid>
<deliveryid>
<deliverynumber>2</deliverynumber>
</deliveryid>
</case>
</Bill>
</Document>
source with wrong output:
<?xml version="1.0"?>
<Document>
<Order>
<OrderID>1111</OrderID>
<case>
<caseid>1111</caseid>
<deliveryid>
<deliverynumber>2</deliverynumber>
</deliveryid>
<deliveryid>
<deliverynumber>2</deliverynumber>
</deliveryid>
<deliveryid>
<deliverynumber>2</deliverynumber>
</deliveryid>
<deliveryid>
<deliverynumber>2</deliverynumber>
</deliveryid>
<deliveryid>
<deliverynumber>2</deliverynumber>
</deliveryid>
<deliveryid>
<deliverynumber>2</deliverynumber>
</deliveryid>
<deliveryid>
<deliverynumber>2</deliverynumber>
</deliveryid>
<deliveryid>
<deliverynumber>2</deliverynumber>
</deliveryid>
<deliveryid>
<deliverynumber>1</deliverynumber>
</deliveryid>
<deliveryid>
<deliverynumber>2</deliverynumber>
</deliveryid>
<deliveryid>
<deliverynumber>2</deliverynumber>
</deliveryid>
<deliveryid>
<deliverynumber>1</deliverynumber>
</deliveryid>
<deliveryid>
<deliverynumber>1</deliverynumber>
</deliveryid>
<deliveryid>
<deliverynumber>1</deliverynumber>
</deliveryid>
<deliveryid>
<deliverynumber>1</deliverynumber>
</deliveryid>
<deliveryid>
<deliverynumber>1</deliverynumber>
</deliveryid>
<deliveryid>
<deliverynumber>2</deliverynumber>
</deliveryid>
<deliveryid>
<deliverynumber>1</deliverynumber>
</deliveryid>
<deliveryid>
<deliverynumber>1</deliverynumber>
</deliveryid>
<deliveryid>
<deliverynumber>1</deliverynumber>
</deliveryid>
<deliveryid>
<deliverynumber>1</deliverynumber>
</deliveryid>
<deliveryid>
<deliverynumber>1</deliverynumber>
</deliveryid>
</case>
<case>
<caseid>2222</caseid>
<deliveryid>
<deliverynumber>1</deliverynumber>
</deliveryid>
<deliveryid>
<deliverynumber>1</deliverynumber>
</deliveryid>
<deliveryid>
<deliverynumber>1</deliverynumber>
</deliveryid>
<deliveryid>
<deliverynumber>1</deliverynumber>
</deliveryid>
</case>
</Bill>
</Document>
Hi,
Try this modified UDF:
if(input.length == 0)
return;
String tmp = input[0];
ArrayList<String> al = new ArrayList<String>();
int i = 0;
while(i < input.length)
{
if(tmp.compareTo(input[i]) == 0)
{
result.addValue(input[i]);
for(int j = i + 1; j < input.length; j++)
{
i = j;
result.addValue(input[j]);
if(input[j].compareTo(ResultList.CC) == 0)
break;
if(j == input.length - 1 && al.size() >0)
result.addContextChange();
}
}
else
{
al.add(input[i]);
for(int j = i + 1; j < input.length; j++)
{
i = j;
al.add(input[j]);
if(input[j].compareTo(ResultList.CC) == 0)
break;
}
}
i++;
}
if(al.size() > 0)
splitDelivery(al.toArray(new String[0]), result, container);
The key difference was that the counters for i were always set at lines 13 & 26 and the couple of lines at 17-18 to ensure that a context change gets inserted when you iterate to the end of the array. By default I think system does not have a context change here because it is not necessary but becomes necessary with your requirement for rearranging the context orders to group at the idoc level. I had to type this free hand because the copy/paste was failing so let me know if you are having any trouble with compilation on your mapping. And you have a great weekend also
Regards,
Ryan Crosby
Message was edited by: Ryan Crosby
Hi Ryan,
Apologies for asking help again.
We encountered an error for this sample scenario:
<?xml version="1.0"?>
<Document>
<Order>
<OrderID>1111</OrderID>
<case>
<caseid>1111</caseid>
<deliveryid>
<deliverynumber>11</deliverynumber>
</deliveryid>
</case>
<case>
<caseid>2222</caseid>
<deliveryid>
<deliverynumber>22</deliverynumber>
</deliveryid>
<deliveryid>
<deliverynumber>11</deliverynumber>
</deliveryid>
</case>
<case>
<caseid>3333</caseid>
<deliveryid>
<deliverynumber>11</deliverynumber>
</deliveryid>
</case>
</Order>
</Document>
All the delivery segments were included in the 1st idoc. the 2nd idoc has no delivery segment. it should have one because of the 22 delivery number in case 2222.
thank you friend.
Hi,
One more minor adjustment to processing order for a different context ordering scenario:
if(input.length == 0)
return;
String tmp = input[0];
ArrayList<String> al = new ArrayList<String>();
int i = 0;
while(i < input.length)
{
if(tmp.compareTo(input[i]) == 0)
{
result.addValue(input[i]);
for(int j = i + 1; j < input.length; j++)
{
i = j;
result.addValue(input[j]);
if(input[j].compareTo(ResultList.CC) == 0)
break;
}
if(i == input.length - 1 && al.size() > 0 && input[i].compareTo(ResultList.CC) != 0)
result.addContextChange();
}
else
{
al.add(input[i]);
for(int j = i + 1; j < input.length; j++)
{
i = j;
al.add(input[j]);
if(input[j].compareTo(ResultList.CC) == 0)
break;
}
}
i++;
}
if(al.size() > 0)
splitDelivery(al.toArray(new String[0]), result, container);
Lines 18-19 were pulled out of the for loop to make sure they always execute for each match of the current delivery context number. In that data arrangement the for loop was not executing so the additional context change was not getting inserted. I also added an additional check to make sure the additional context change would only be added if the last entry was not already a context change. This is because it is possible for the last entry on the recursive method calls to be a context change and you wouldn't want duplicate context changes inserted and mess up the XML target data.
Regards,
Ryan Crosby
Message was edited by: Ryan Crosby
Sorry Ryan but we still encountered errors
<?xml version="1.0"?>
<Document>
<Order>
<OrderID>1111</OrderID>
<case>
<caseid>1111</caseid>
<deliveryid>
<deliverynumber>22</deliverynumber>
</deliveryid>
</case>
<case>
<caseid>2222</caseid>
<deliveryid>
<deliverynumber>22</deliverynumber>
</deliveryid>
<deliveryid>
<deliverynumber>22</deliverynumber>
</deliveryid>
<deliveryid>
<deliverynumber>22</deliverynumber>
</deliveryid>
</case>
<case>
<caseid>3333</caseid>
<deliveryid>
<deliverynumber>22</deliverynumber>
</deliveryid>
<deliveryid>
<deliverynumber>11</deliverynumber>
</deliveryid>
</case>
</Order>
</Document>
In the scenario above, the wrong deliveries were assisnged to the wrong case.
the output for the above is as follows:
idoc
case
caseid 3333
delivery
deliveryid 11
idoc
case
caseid 1111
delivery
deliveryid 22
delivery
deliveryid 22
delivery
deliveryid 22
case
caseid 2222
delivery
deliveryid 22
case
caseid 3333
delivery
deliveryid 22
it should have been
idoc
case
caseid 3333
delivery
deliveryid 11
idoc
case
caseid 1111
delivery
deliveryid 22
case
caseid 2222
delivery
deliveryid 22
delivery
deliveryid 22
delivery
deliveryid 22
case
caseid 3333
delivery
deliveryid 22
The two deliveryids got mixed up with the other caseid. im not sure how to adjust the code in order to fix the context change.
sorry about this. you've helped enough already.
Hi,
I was holding off on cooking up a set of queues related to caseid but I think it'll be the best approach to handle all the possible data arrangements. Stay tuned over the next day or two and I'll get something for you, but it may require the use of a Function Library instead of simple UDFs within the message mapping. This is happening because the context counts by unique delivery number were coming in the order of 1-3-1-1 but for the rearrangement the result needed to be 1-1-3-1. The counts of 1 highlighted in red represent where the counts for delivery number 1 were vs. should be. This could be fixed by re-ordering the queue to go in the order of smallest first value to highest but the set of queues is a more sturdy solution to the problem.
Regards,
Ryan Crosby
Hi,
Here is an adjusted map that should work no matter which deliveries you have matched to whatever cases in any order. First I'll note that for the previous UDFs you can delete splitDelivery & splitCaseByDelivery because I have setup a Function Library to manage the context rearrangement. See below for the adjusted maps for caseid & deliveryid:
caseid -
deliveryid -
Here are the function library details:
Attributes and Methods -
private HashMap<String, LinkedList<ArrayList<String>>> hm;
init -
hm = new HashMap<String, LinkedList<ArrayList<String>>>();
cleanup -
hm = null;
getQueueContexts -
// Get key and retrieve list of values from queue context
for(int i = 0; i < keys.length; i++)
{
LinkedList<ArrayList<String>> ll = hm.get(keys[i]);
ArrayList<String> al = ll.removeFirst();
// Add values to context
for(int j = 0; j < al.size(); j++)
result.addValue(al.get(j));
// Check size of queue
if(ll.size() == 0)
hm.remove(ll);
// Check hash map size - if there are still values then insert context change
if(hm.size() != 0)
result.addContextChange();
}
buildNodeMap -
// Insert or modify keys
int counter = 0;
ArrayList<String> al;
LinkedList<ArrayList<String>> ll;
// Iterate keys - if exists then modify otherwise insert new entry
for(int i = 0; i < keys.length; i++)
{
result.addValue(keys[i]);
al = new ArrayList<String>();
// Get or create new LinkedList (Queue)
if(hm.containsKey(keys[i]))
{
ll = hm.get(keys[i]);
}
else
{
ll = new LinkedList<ArrayList<String>>();
hm.put(keys[i], ll);
}
ll.add(al);
// Add list values for context length
while(counter < vals.length)
{
if(vals[counter].compareTo(ResultList.CC) != 0)
{
al.add(vals[counter]);
}
else
{
counter++;
break;
}
counter++;
}
}
Regards,
Ryan Crosby
Hi Ryan,
Kindly see XML below:
<?xml version="1.0"?>
<Document>
<Order>
<OrderID>19</OrderID>
<case>
<caseID>04</caseID>
<delivery>
<deliveryid>80</deliveryid>
</delivery>
</case>
<case>
<delivery>
<deliveryid>80</deliveryid>
</delivery>
<delivery>
<deliveryid>80</deliveryid>
</delivery>
<delivery>
<deliveryid>80</deliveryid>
</delivery>
<delivery>
<deliveryid>80</deliveryid>
</delivery>
</case>
<case>
<caseID>06</caseID>
<delivery>
<deliveryid>80</deliveryid>
</delivery>
</case>
<case>
<caseID>07</caseID>
<delivery>
<deliveryid>80</deliveryid>
</delivery>
<delivery>
<deliveryid>80</deliveryid>
</delivery>
<delivery>
<deliveryid>80</deliveryid>
</delivery>
<delivery>
<deliveryid>80</deliveryid>
</delivery>
<delivery>
<deliveryid>80</deliveryid>
</delivery>
<delivery>
<deliveryid>80</deliveryid>
</delivery>
<delivery>
<deliveryid>80</deliveryid>
</delivery>
<delivery>
<deliveryid>80</deliveryid>
</delivery>
<delivery>
<deliveryid>80</deliveryid>
</delivery>
<delivery>
<deliveryid>80</deliveryid>
</delivery>
<delivery>
<deliveryid>80</deliveryid>
</delivery>
<delivery>
<deliveryid>80</deliveryid>
</delivery>
<delivery>
<deliveryid>80</deliveryid>
</delivery>
<delivery>
<deliveryid>80</deliveryid>
</delivery>
</case>
<case>
<caseID>11</caseID>
<delivery>
<deliveryid>80</deliveryid>
</delivery>
<delivery>
<deliveryid>77</deliveryid>
</delivery>
</case>
</Order>
</Document>
In my output, the order of the deliveryid values are correct. the grouping has errors though. the mapping for delivery is incorrect. the case 11 should be on top with 1 delivery. so the order should be, 1, 1, 4, 1, 14, 1.. the order instead was 1,4,1,14,1,1.
Hi,
My XML structure was slightly different from yours so that mapping for deliveryid that I mentioned above is for the delivery segments. My structure was like this:
<case>
<caseid/>
<deliveryid>
<deliverynumber>
</deliverynumber>
</deliveryid>
</case>
So the mapping for the actual delivery numbers was very simple and a sort of all values with a split by value. The only complicated pieces were for case ids and delivery segments which are the two mappings I included screenshots of in the previous post.
Regards,
Ryan Crosby
Hi,
I would suggest to use a intermidiat structure and mapping to group the delivery numbers and caseid.
you can have a structure
Inter
header
case
caseid
delivery
deliverynumber
so you need to populate inter for every unique delivery number. mapping is like below
delivery number --> remove context --> Sort --> split by value (value change) --> Collaps context --> Iner
then you need to sort the case id in the same way as delivery no
Case ID - -> Sort by key -->
delivery number --> remove context -->
delivery number --> remove context --> Sort --> split by value (value change) --> Format by example
--> Map to case and case id
mapping of delivery number
delivery number --> remove context --> Sort --> split by value (each value) -->delivery number
Please let me know if you phase any issue.
regards,
Harish
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
User | Count |
---|---|
94 | |
11 | |
10 | |
9 | |
9 | |
7 | |
6 | |
5 | |
4 | |
4 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.