on 08-16-2011 6:44 AM
Hi,
I am working on JDBC to File scenario.
There is a staging table in SQL Server. XI polls the table every 20 secs and picks up all the data with flag = 0. After fetching the records it sets the flag to 1 so that the same data do not get fetched the next time.
The record have one Bill No and there can be more than 1 line items associated with that bill no.
The target structure is as follows:
Record 1
Bill No 0...unbounded
Line item 1
Line items 0...unbounded
Now the requirement is to generate a file with header and all the corresponding line items below it. That is one file can have many headers but all the line items should succeed its corresponding header.
I have written a UDF to generate the same.
For Bill No.:
Bill No -> RemoveContext -> Sort - > UDF -> SplitBy Value -> CollapseContext
UDF just removes the duplicate Bill No and generates the list.
Now the issue is if Bill No as per source is 8,4,1,3 then the ones getting generated in the target str is in the same order as it is in source that is 8,4,1,3. But I want this in the sorted order ie. 1,3,4,8. Althoug I have used Sort func, its not getting generated.
Please help.
Record Node -> RemoveContext -> Sort - > SplitBy Value -> CollapseContext --> Record Node
WIth this your header and Items are gets sorted out.
And then now you can use the below logic for required output:
Bill No -> RemoveContext -> SortByKey - > SplitBy Value*(Eachvalue) -> FormatByExample -
BillDesp
Bill Desp --> RemoveContext -> Sort - > UDF -> SplitBy Value*(Eachvalue)
Basing upon Bill No each of the other field values will be effectively sorted out. So, you need to apply this for other fields too.
Regards
Pothana
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
Hi Pothana,
The Record field is the Message type of the traget str.
For Bill No we are using collapse context coz we want to remove the duplicates, We want 1 value to occur only once.
But Line Item, each time they occur with that bill no, we have to add that line item with corresponding bill no heasder.
Eg
Source:
Bill 8 Line Item 1
Bill 5 Line item 2
Bill 8 Line item 2
Traget:
Bill 5 Line Item 2
Bill 8 Line Item 1
Line Item 2
My UDF for Bill goes like this:
public void removeDuplicates(String[] value,ResultList result,Container container){
String temp = value[0];
result.addValue(value[0]);
for(int i=0;i<value.length;i++)
{
if(!(value<i>.equals(temp)))
{
result.addValue(value<i>);
}
temp=value<i>;
}
For Line Items(1), no mapping is used as it will be generated irrespective of anything.
For Line Item (0...unbounded) i am using following logic:
Bill No -> removeContext -> sort -> Res1
Bill No -> removeContext -> SortByKey -> Res 2
BillSerialNo(line item) -> removeContext ->
Res1
Res2 ---> UDF -> Line Item
UDF goes like: (Its checking the billNo first. If two bill Nos are same generate the line item for that bill no)
String temp = billNo[0];
for(int i=0;i<billNo.length;i++)
{
if(!billNo<i>.equals(temp))
{
result.addContextChange();
}
result.addValue(billSerialNo<i>);
temp=billNo<i>;
}
chk this:
BillNo-removeContext--sort--splitByValue(valuechange)---collapse Context---Record
BillNo-removeContext--sort--splitByValue(valuechange)---collapse Context---splitbyvalue(each Value)---BillNo(Target)
BillNo-removeContext---------------------------
-------------------------- SortByKey----------FormatByExample(2nd argument mapping below)---UDF2---Line Item
BillSerailNo(set its context to Row)--UDF1---removeContext--
BillNo-removeContext--sort--splitByValue(valuechange)----2nd argument mapping of "formatbyexample"
BillNo-removeContext---
--------SortByKey----FormatByExample(2nd argument)---UDF2--splitbyvalue(each value)-Bill Serial No
BillSerailNo(set its context to Row)----UDF1---removeContext-----
BillNo-removeContext--sort--splitByValue(valuechange)----2nd argument mapping of "formatByExample"
udf1: Input will be var1
Execution type: all values of a context
String a="";
for(int i=0;i<var1.length;i++)
{
a = a + var1<i>+ ";";
}
int b= a.length();
result.addValue(a.substring(0,b-1));
UDF2: input will be var1
Execution type: all values of a context
for(int b=0;b<var1.length;b++)
{
String [] a = var1<b>.split(";");
for(int i=0;i<a.length;i++)
{
result.addValue(a<i>);
}
}
Input:
<MT_Source>
<ROW>
<Bill_No>8</Bill_No>
<LineItems>
<BillSerailNo>second</BillSerailNo>
<LineItems>
<LineItems>
<BillSerailNo>second1</BillSerailNo>
</LineItems>
</Row>
<ROW>
<Bill_No>5</Bill_No>
<LineItems>
<BillSerailNo>First</BillSerailNo>
<LineItems>
<LineItems>
<BillSerailNo>First1</BillSerailNo>
</LineItems>
</Row>
<ROW>
<Bill_No>8</Bill_No>
<LineItems>
<BillSerailNo>Third</BillSerailNo>
<LineItems>
<LineItems>
<BillSerailNo>Third1</BillSerailNo>
</LineItems>
</Row>
<MT_Source>
Target:
<MT_Target>
<Record>
<Bill_No>5</Bill_No>
<LineItems>
<BillSerailNo>First</BillSerailNo>
<LineItems>
<LineItems>
<BillSerailNo>First1</BillSerailNo>
</LineItems>
</Record>
<Record>
<Bill_No>8</Bill_No>
<LineItems>
<BillSerailNo>second</BillSerailNo>
<LineItems>
<LineItems>
<BillSerailNo>second1</BillSerailNo>
</LineItems>
<LineItems>
<BillSerailNo>Third</BillSerailNo>
<LineItems>
<LineItems>
<BillSerailNo>Third1</BillSerailNo>
</LineItems>
</Record>
<MT_1>
Hi..
try to use sort function after UDF and debug your mapping after UDF how it is genarating.
Regards,
Raj
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
User | Count |
---|---|
101 | |
13 | |
13 | |
11 | |
11 | |
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.