Concatenate an entire column into a single cell

[Former Member]

Hi Experts,

I have a requirement to concatenate multiple values into one cell and have them grouped by a primary dimension. I have seen a solution for BOXI 3.1 but when I try it in 4.1, I don't seem to get the same results. I was wondering if there was a new work around for this solution?

Here is a sample of how my data is structured...

Animal	Owners
Dogs	Bob
Cats	Matt
Goats	Mike
Chickens	Jake
Dogs	Ryan
Cats	Brian
Goats	Jeff
Chickens	Christine
Dogs	Alex
Goats	Olaf
Goats	Victor
Cats	Vanessa
Cats	Lilly
Cats	Mackenzie
Cats	Jackie

Here is what I am trying to accomplish...

Animal	Owners
Dogs	Bob, Ryan, Alex
Cats	Matt, Brian, Vanessa, Lilly, Mackenzie, Jackie
Goats	Mike, Victor, Olaf, Jeff
Chickens	Christine

The solution I found that doesn't work in 4.1 is this...

·        Create a variable [VAR Max CATEGORY = Max([Category]) In ([Lines]) to find the maximum CATEGORY with respect to each Line

·        Create a variable [VAR Concat CATEGORY = CATEGORY +", "+ Previous(Self), to concatenate the CATEGORY value with it's previous row value

·        Create a 3rd variable [VAR Max Concat CATEGORY = [VAR Concat CATEGORY Where ([Category]=[VAR Max Category]). This variable basically gives only those rows of [VAR Concat CATEGORY where the value of the CATEGORY is maximum for that particular Line

·        Create a variable [VAR CATEGORY =If(IsNull(Previous([VAR Max Concat Category]));Substr([VAR Max Concat Category];1;Length([VAR Max Concat Category])-2);Substr([VAR Max Concat Category];1;Pos([VAR Max Concat Category];Previous([VAR Max Concat Category]))-3))

Basically, the formula checks if the previous value of VAR Max Concat Category is NULL.

Source: http://f100smai.benxbrain.com/en/FALSE-How-do-I-concatenate-a-single-field-from-multiple-rows-in-BOX...

Thanks in advance!

Best,
Ryan

[Former Member · Accepted solution]

Hi Ryan,

The document link which you referred should work as you intend like below. I tried in BO 4.0 its works for me.

Create 4 Variables which includes 1 Measure and 3 Dimension Variables

1. MaxOwner

= Max([Owner]) In ([Animals])

2. ConcatOwner

=[Owner] +", "+ Previous(Self)

3. MaxConcatOwner

= [ConcatOwner] Where ([Owner]=[MaxOwner])

4. FinalOwner

=If(IsNull(Previous([MaxConcatOwner]));Substr([MaxConcatOwner];1;Length([MaxConcatOwner])-2);Substr([MaxConcatOwner];1;Pos([MaxConcatOwner];Previous([MaxConcatOwner]))-3))

Drag the Animal Object and then the FinalOwner Variable to Vertical Table will retrieve information which you want to achieve.

---Raji. S

[Former Member · Accepted solution]

Hi Raji,

Thank you for clarifying that this worked for you. I was able to use your formulas to get what I needed. I realized there is not enough data for the data set that I am using. So I imported an excel spreadsheet and used this formulas and it worked like a charm!

Best,
Ryan

[Former Member]

Hi Ryan,

If your DB is oracle 11G R2 , then use "LISTAGG" function as below. it give the same output as you expect.

select    Animal,

              LISTAGG( Owners, ',') WITHIN GROUP (ORDER BY Animal) As "Owners_List"

FROM    From_table_name

Group by Animal;

Regards,

Shanmuga

[Former Member]

select animal, wa_owners(' ' || owners) as owners from your_table

group by animal:

[Former Member]

In this link, some user explained the changes he made to the above formula to make it work.

Following link may help:

http://bi.srivatsakr.com/2011/08/converting-rows-into-single-cell-comma.html