Home » Archived » BIRT » Use computed column to concatenate data set data
| |
| Re: Use computed column to concatenate data set data [message #873188 is a reply to message #872826] |
Thu, 17 May 2012 16:42   |
|
Neil
Use the concatenate aggregate function with an aggregation element.
Look at the attached example.
Jason
On 5/16/2012 6:30 PM, Neil Wang wrote:
> Hi,
>
> I am trying to create a string which is a concatenation of all the data
> in a data set.
> For example, I have a data set "row[FirstName]" and it has data of
> "John", "Alice" and "Mary". I would like to create a string which is
> "John, Alice, Mary" (with comma in between data).
>
> I am thinking about using computed column function in data set to
> achieve this but don't know how to grab each data from the data set.
>
> please advise.
>
> Neil
|
|
|
| Re: Use computed column to concatenate data set data [message #873191 is a reply to message #872826] |
Thu, 17 May 2012 16:42 |
|
Neil
Use the concatenate aggregate function with an aggregation element.
Look at the attached example.
Jason
On 5/16/2012 6:30 PM, Neil Wang wrote:
> Hi,
>
> I am trying to create a string which is a concatenation of all the data
> in a data set.
> For example, I have a data set "row[FirstName]" and it has data of
> "John", "Alice" and "Mary". I would like to create a string which is
> "John, Alice, Mary" (with comma in between data).
>
> I am thinking about using computed column function in data set to
> achieve this but don't know how to grab each data from the data set.
>
> please advise.
>
> Neil
|
|
|
| Re: Use computed column to concatenate data set data [message #873193 is a reply to message #872826] |
Thu, 17 May 2012 16:42 |
|
Neil
Use the concatenate aggregate function with an aggregation element.
Look at the attached example.
Jason
On 5/16/2012 6:30 PM, Neil Wang wrote:
> Hi,
>
> I am trying to create a string which is a concatenation of all the data
> in a data set.
> For example, I have a data set "row[FirstName]" and it has data of
> "John", "Alice" and "Mary". I would like to create a string which is
> "John, Alice, Mary" (with comma in between data).
>
> I am thinking about using computed column function in data set to
> achieve this but don't know how to grab each data from the data set.
>
> please advise.
>
> Neil
|
|
|
| Re: Use computed column to concatenate data set data [message #873194 is a reply to message #873026] |
Thu, 17 May 2012 16:43 |
|
Sorry Tomas, I did not see that you had replied.
Jason
On 5/17/2012 6:52 AM, Tomas Greif wrote:
> Hi,
>
> you can use "CONCATENATE" as agregation function. Create new computed
> column with row[FirstName] as Expression, in Aggregation choose
> "CONCATENATE" and type "," as Separator.
> Tomas
|
|
|
| Re: Use computed column to concatenate data set data [message #873654 is a reply to message #873026] |
Fri, 18 May 2012 19:14 |
 Neil Wang
Messages: 105
Registered: July 2009 |
Senior Member |
|
|
Hi Thomas,
Thank you for your comment and it works.
I have another question.
I have a data set with this information
Child data set
Key|Value|ID|Concatenation
AAA|BBBBB|01|AAABBBBB, CCCDDDDD
CCC|DDDDD|01|AAABBBBB, CCCDDDDD
The goal of the concatenation is to concatenate all key and value columns into one string which works fine.
I have another data set like this
Parent data set
Name|ID
Bill|01
I need to create a join data set with ID as the join key and the result looks like
Parent:Name|Parent:ID|Child:Key|Child:Value|Child:ID|Child:Concatenation
Bill| 01| AAA| BBBBB| 01| AAABBBBB, CCCDDDDD
Bill| 01| CCC| DDDDD| 01| AAABBBBB, CCCDDDDD
However, I would like to have the result to look like
Parent:Name|Parent:ID|Child:Key|Child:Value|Child:ID|Child:Concatenation
Bill| 01| AAA| BBBBB| 01| AAABBBBB, CCCDDDDD
I am not gonna display Child:Key and Child:Value in my report; therefore, I don't mind having/not having them in the final result set. The whole purpose is to have one parent to have its "Key + Value" concatenated in one string; therefore, having two rows defeats the purpose as I put them in the detailed row of a table, both rows will show up. I am thinking I should keep just one row in the child result set; so that, the final result set will have one row only; however, I am not sure how to do it. Please advise.
Neil
|
|
|
| Re: Use computed column to concatenate data set data [message #876073 is a reply to message #873654] |
Wed, 23 May 2012 21:11 |
|
Neil
Did you get this to work?
Jason
On 5/18/2012 3:14 PM, Neil Wang wrote:
> Hi Thomas,
>
> Thank you for your comment and it works.
>
> I have another question.
>
> I have a data set with this information
>
> Child data set
> Key|Value|ID|Concatenation
> AAA|BBBBB|01|AAABBBBB, CCCDDDDD
> CCC|DDDDD|01|AAABBBBB, CCCDDDDD
>
> The goal of the concatenation is to concatenate all key and value
> columns into one string which works fine.
> I have another data set like this
>
> Parent data set
> Name|ID
> Bill|01
>
> I need to create a join data set with ID as the join key and the result
> looks like
>
> Parent:Name|Parent:ID|Child:Key|Child:Value|Child:ID|Child:Concatenation
> Bill| 01| AAA| BBBBB| 01| AAABBBBB, CCCDDDDD
> Bill| 01| CCC| DDDDD| 01| AAABBBBB, CCCDDDDD
>
> However, I would like to have the result to look like
> Parent:Name|Parent:ID|Child:Key|Child:Value|Child:ID|Child:Concatenation
> Bill| 01| AAA| BBBBB| 01| AAABBBBB, CCCDDDDD
>
> I am not gonna display Child:Key and Child:Value in my report;
> therefore, I don't mind having/not having them in the final result set.
> The whole purpose is to have one parent to have its "Key + Value"
> concatenated in one string; therefore, having two rows defeats the
> purpose as I put them in the detailed row of a table, both rows will
> show up. I am thinking I should keep just one row in the child result
> set; so that, the final result set will have one row only; however, I am
> not sure how to do it. Please advise.
>
> Neil
>
|
|
| | |
Goto Forum:
Current Time: Wed Apr 24 23:34:09 GMT 2024
Powered by FUDForum. Page generated in 0.03741 seconds |
] 
Search
Help
Register
Login
Home
