Concatenating values in multiple rows to single field in Target.

[lnirmala]

Hi all,

I have a scenario in which i am taking data from Database and giving output in the form of XML file.

My source format is as given below(Occurences are 1 for all the fields):

Row

Date

ShipName

CargoType

AgentName

Now i need to get the target format as given below(Occurences are 1 for all the fields):

Data

Date

Day

ExpectedArrivalTime

Ship

ExpCargo

Suppose i am getting source data like this

<Row>

<Date>2009-12-03 12:35:45</Date>

<ShipName>Vessel1</ShipName>

<CargoType>Cargo1</CargoType>

<AgentName>Agent1</AgentName>

</Row>

<Row>

<Date>2009-12-03 12:35:45</Date>

<ShipName>Vessel1</ShipName>

<CargoType>Cargo2</CargoType>

<AgentName>Agent1</AgentName>

</Row>

<Row>

<Date>2009-12-03 11:35:45</Date>

<ShipName>Vessel1</ShipName>

<CargoType>Cargo3</CargoType>

<AgentName>Agent1</AgentName>

</Row>

Outpt data should be in the format:

<Data>

<Date>2009-12-03</Date>

<Day>

<ExpectedArrivalTime>12:35</ExpectedArrivalTime>

<Ship>Vessel1</Ship>

<ExpCargo>Cargo1/Cargo2</ExpCargo>

</Day>

<Day>

<ExpectedArrivalTime>11:35</ExpectedArrivalTime>

<Ship>Vessel1</Ship>

<ExpCargo>Cargo3/Cargo2</ExpCargo>

</Day>

Could someone throw some light on how to concatenate the cargo field.

Thanks.

[Former Member · Accepted solution]

Hi,

Writing a small udf would be the easiest way out. Something like:

for (int i = 0; i < a.length; i++)

{

String str = res+a [ i ];

}

Regards,

Siddhesh S.Tawate

Edited by: siddhesh tawate on Oct 14, 2009 2:53 PM

[lnirmala · Accepted solution]

Hi Sidhesh,

In the begining there was no client req like concatenation of cargo field.So i have done mapping as given below:

Date>substring(0..10)>removeContext>sort>SplitByValue(Value change)>collapseContext>Data

Date>substring(0..10)>removeContext>sort>SplitByValue(Value change)>collapseContext>SplitByValue(each Value)>Transform>Date

Date>removeContext>sort>substring(0..10)>SplitByValue(Value change)-->Day

Date>removeContext

---

SortByKey>substring(11,16)>SplitByValue(eachValue)>ExpectedArrivalTime

Date>removeContext--

---

I

Date>removeContext

---

SortByKey>SplitByValue(eachValue)-->Ship

Date>removeContext--

---

I

Date>removeContext

---

SortByKey>SplitByValue(eachValue)-->Cargo

Date>removeContext--

---

I

But now after testing they came with this requirement.So i am confused from where i should start.Because i want the existing functionality to remain the same but only for those ships which are having multiple rows on the same date and time concatenation needs to be carried out.

Thankyou.

[Former Member]

Hi Lekshmi,

Please share us the UDF's along with the output screenshot.

Thanks....

[lnirmala]

Solved the problem by writting some UDF's.

[Former Member]

if the input is

<Row>
<Date>2009-12-03 12:35:45</Date>
<ShipName>Vessel1</ShipName>
<CargoType>Cargo1</CargoType>
<AgentName>Agent1</AgentName>
</Row>
<Row>
<Date>2009-12-03 12:35:45</Date>
<ShipName>Vessel1</ShipName>
<CargoType>Cargo2</CargoType>
<AgentName>Agent1</AgentName>
</Row>
<Row>
<Date>2009-11-03 11:35:45</Date>
<ShipName>Vessel2</ShipName>
<CargoType>Cargo3</CargoType>
<AgentName>Agent1</AgentName>
</Row>
<Row>
<Date>2009-11-03 11:35:45</Date>
<ShipName>Vessel2</ShipName>
<CargoType>Cargo4</CargoType>
<AgentName>Agent1</AgentName>
</Row>

then How should be your output

I want to know whether all Ship will be on the same date but different timings

[Former Member]

You can do this with the Node functions. Here are some resources that will help you understand if you've never tried before:

[Message Mapping|http://www.sdn.sap.com/irj/scn/weblogs?blog=/pub/wlg/2171] [original link is broken] [original link is broken] [original link is broken];

[Message Mapping with Node Functions|http://www.sdn.sap.com/irj/scn/weblogs?blog=/pub/wlg/2792] [original link is broken] [original link is broken] [original link is broken];

[XI/PI: Introduction to Context Handling in Message Mapping|http://www.riyaz.net/blog/xipi-introduction-to-context-handling-in-message-mapping/]

[Former Member]

Hi Aaron,

None of these blogs describe how above requirement can be met. A little specific suggestion would be more helpful.

I really doubt if values within same field at different indexes can be concatenated using standard XI functions.

Regards,

Siddhesh S.Tawate

[Former Member]

None of these blogs describe how above requirement can be met. A little specific suggestion would be more helpful.
I really doubt if values within same field at different indexes can be concatenated using standard XI functions.

You may be able to by sorting, combining context, then concatenating. Experiement and see if you can get close. If not then a UDF can do it as already suggested.

Also, here is a post that helps to understand how to work with node functions and a UDF.

[http://www.riyaz.net/blog/xipi-convert-flat-file-to-deeply-nested-xml-structures-using-only-graphical-mapping/]