Concatenation of Values

building semantic layer
1

I have a need to concatenate all the values chosen in one object. For
example, there are several topics a user can mark off for a particular
customer, however, when I pull a report, a row is listed for every topic
that is checked off, even though it is the same customer. Like this:
Customer Name Topic 1
Customer Name Topic 2
Customer Name Topic 3

Is there a way to have the values concatenate so that there is only one row
per customer, with all of the values in one cell? Like this:
Customer Name Topic 1, Topic 2, Topic 3.

I can’t do a group data because there are hundreds of combinations. Anyone
have any ideas?
Thanks
Michele Robertson
MKIS, SalesTrak Reporting
PAX 47573

Listserv Archives (BOB member since 2002-06-25)

2

How about creating a new object in the Universe? In Designer, right click
on the desired class and select Insert Object. Then, right click on the new
object and select Object Properties. Rename the new object then click the
“>>” to the far right of “Select”. Pick the 3 topic objects from Classes
and Objects list and concatenate them using the “||” operator. You might
want to use a “;” as a separator between them.

George Baranowski
QuadraMed

I have a need to concatenate all the values chosen in one object. For
example, there are several topics a user can mark off for a particular
customer, however, when I pull a report, a row is listed for every topic
that is checked off, even though it is the same customer. Like this:
Customer Name Topic 1
Customer Name Topic 2
Customer Name Topic 3

Is there a way to have the values concatenate so that there is only one row
per customer, with all of the values in one cell? Like this:
Customer Name Topic 1, Topic 2, Topic 3.

I can’t do a group data because there are hundreds of combinations. Anyone
have any ideas?
Thanks
Michele Robertson

Listserv Archives (BOB member since 2002-06-25)

3

Before you build an object in Designer with concatenation, make sure the
database platform you are using is capable of interpreting the SQL generated
in the GROUP BY clause. For example, we are using Rdb and are not able to
use concatenated objects in Designer as we get an SQL error on execution of
a query if the concatenated object is in the GROUP BY clause.
Crystal Golding
Senior Systems Analyst
Management Information Section
University of Queensland
Brisbane Australia 4072
E-mail: c.golding@mailbox.uq.edu.au
Web: www.mis.jdstory.uq.edu.au
Phone: +61 7 336 57289
Fax: +61 7 336 58202

Listserv Archives (BOB member since 2002-06-25)

4

Sorry, I may not have described it well enough. It is only one object, but
I want the values with in the object to concatenate. So if the Object is
Topics, and the values that were chosen are One, Two, Three, I want them to
appear in one cell instead of a row for each i.e.
One
Two
Three
Anymore ideas?

From: George Baranowski[SMTP:gbaranowski@QUADRAMED.COM]
Sent: Tuesday, January 05, 1999 4:38 PM

How about creating a new object in the Universe? In Designer, right click
on the desired class and select Insert Object. Then, right click on the
new
object and select Object Properties. Rename the new object then click the
“>>” to the far right of “Select”. Pick the 3 topic objects from Classes
and Objects list and concatenate them using the “||” operator. You might
want to use a “;” as a separator between them.

George Baranowski
QuadraMed

I have a need to concatenate all the values chosen in one object. For
example, there are several topics a user can mark off for a particular
customer, however, when I pull a report, a row is listed for every topic
that is checked off, even though it is the same customer. Like this:
Customer Name Topic 1
Customer Name Topic 2
Customer Name Topic 3

Is there a way to have the values concatenate so that there is only one
row
per customer, with all of the values in one cell? Like this:
Customer Name Topic 1, Topic 2, Topic 3.

I can’t do a group data because there are hundreds of combinations.
Anyone
have any ideas?
Thanks
Michele Robertson

Pls report bounces in response to postings to
BUSOB-L-Request@listserv.aol.com
Web archives (9am-5pm ET only): listserv.aol.com/archives/busob-l.html
OR search: Mail to listserv@listserv.aol.com, ‘search a_phrase in
BUSOB-L’
Unsubscribe: Mail to listserv@listserv.aol.com, ‘unsubscribe BUSOB-L’
====================================

Listserv Archives (BOB member since 2002-06-25)

5

“Robertson, Michele R” wrote:

I have a need to concatenate all the values chosen in one object. For
example, there are several topics a user can mark off for a particular
customer, however, when I pull a report, a row is listed for every topic
that is checked off, even though it is the same customer. Like this:
Customer Name Topic 1
Customer Name Topic 2
Customer Name Topic 3

Is there a way to have the values concatenate so that there is only one row
per customer, with all of the values in one cell? Like this:
Customer Name Topic 1, Topic 2, Topic 3.

Yes, there is.

A) Report Version:
You can define some local variable as:
topic1 = if customer = previous() then previous()&", " else
“”
topic2 = if customer = previous(previous()) then
previous(previous()) & ", " else “”
topic2 = if customer = previous(previous(previous())) then
previous(previous(previous())) & ", " else “”
…

and the one you display in your report:
all_topics = topic1 & topic2 & topic3 & … & topic_n

But some limitations:
1. maximum number of simultaneousely available topics per customer must be
known in advance
2. data must be sorted on , otherwise the previous() will not work
properly…

B) Database/Designer Version
Create a UNION view in your database which will transpose the “rows of topics” to
“columns” of topics, one row per customer anbd define a new object in designer…

hope, it helps
Walter

Walter Muellner
Delphi Software GmbH, Vivenotgasse 48, A-1120 Vienna, Austria
Tel. +43-1-8151456-12, Fax: +43-1-8151456-21
e-mail: w.muellner@delphi.at, WEB: http://www.delphi.at

Listserv Archives (BOB member since 2002-06-25)

6

Walter M&llner wrote:

“Robertson, Michele R” wrote:

I have a need to concatenate all the values chosen in one object. For
example, there are several topics a user can mark off for a particular
customer, however, when I pull a report, a row is listed for every topic
that is checked off, even though it is the same customer. Like this:
Customer Name Topic 1
Customer Name Topic 2
Customer Name Topic 3

…

But some limitations:
1. maximum number of simultaneousely available topics per customer must be
known in advance
2. data must be sorted on , otherwise the previous() will not work
properly…

I forgot to mention: version A works only if you group on customer and display the
value in the heading/footing row…

PS: I didn’t try it, but I guess, it should work this way.

Walter

Walter Muellner
Delphi Software GmbH, Vivenotgasse 48, A-1120 Vienna, Austria
Tel. +43-1-8151456-12, Fax: +43-1-8151456-21
e-mail: w.muellner@delphi.at, WEB: http://www.delphi.at

Listserv Archives (BOB member since 2002-06-25)