skip to main |
skip to sidebar
Converting rows into a single cell (comma separated list) in WebI
11:20 AM
Posted by Srivatsa Kondapalli
This post describes steps on how to concatenate data in multiple rows into a single cell, as a comma separated list.
I had read a post on this in forumtopics long back, but could not find it when I needed it now. Hence, I experimented on this for a while based on whatever I remembered and found out the way once again. So, I thought of jotting it down here before I forget it, and hopefully someone finds it useful when they are searching for a similar requirement!!
Let us take an example from eFashion universe. I am picking [LINES] and [CATEGORY] objects from the Product class for my example:
In the result set, I am restricting the rows (for simplicity) for LINES = Dresses, Jackets and Leather.
For the above data, what needs to be achieved would look something like below:
A method on how this can be achieved at report level in WebI is explained below:
- 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
The records would look like this:
- 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
On replacing VAR Concat Category with VAR Max Concat Category column in the above screenshot, we get:
I have displayed Category and Var Max Category only for illustration purposes. It is actually not required to be present / displayed in the report. On removing those two columns:
Now, the only pending thing to do is to display those categories that belong to their Lines, against each of their respective Lines. The below formula would do the trick:
- 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.
If TRUE, then it just displays VAR Max Concat Category.
In the above screenshot, this scenario occurs for LINE = Dresses. There is no value for VAR Max Concat Category prior to LINE = Dresses and hence it is NULL.
If FALSE, then previous row value of VAR Max Concat Category is removed from the present row value of VAR Max Concat Category.
This scenario occurs for LINE = Jackets and LINE = Leather (and all the rows that may occur below). This is done with the help of POS (for finding the position where the previous value of VAR Max Concat Category starts), and SUBSTR functions
This is the final output what we intended to achieve at the beginning of this post:
This entry was posted on October 4, 2009 at 12:14 pm, and is filed under
BOXI,
convert rows to columns,
WEBI
. Follow any responses to this post through RSS. You can leave a response, or trackback from your own site.
Subscribe to:
Post Comments (Atom)
December 9, 2011 at 12:02 AM
Hi,
Thanks a lot for your Tutorial. The only problem that I find is that the concatenation is of reverse alphabetical order.
I did a little tweak, it is as below. I hope it'll help you as your post have helped me. =)
1.
[VAR Max Category] = Max([Category]) In ([Lines])
*No change
2.
[VAR Concat Category] = Previous(Self) +", "+ [Category]
*I reversed this to reverse the effect.
3.
[VAR Max Concat Category] = [VAR Concat Category] Where ([Category]=[VAR Max Category])
*No change
4.
[VAR Category]= =Substr([VAR Max Concat Category];3;Length([Var Max Concat Category])-2)
*Since the the preceding first value is null, there will be a leading ", " in front, hence, sub-stringing it from the 3rd char to the last will eliminate the leading unused characters.
January 6, 2012 at 2:27 PM
This is excellent! Thank you!!! Any ideas onto how to make these variables in the universe??
March 24, 2012 at 4:13 PM
Thanks Srivatsa,
It works beautifully.
there were some issues when i worked till [VAR Max Concat Category].
But when i created and worked till the end and created the last variable [VAR Category]
it worked.
I have been looking everywhere on net for this(there 2 solutions 1. using the DB solution and 2.using Universe solution )but i think yours using Webi rocks
May 11, 2012 at 1:06 PM
I encountered one situation where the final variable doesn't work - when the different Lines groupings contain the same Category value. In your clothing example, this wouldn't logically occur ('Skirts' would not show up as a category under the 'Jackets' line), but it certainly can in other data structures.
For example, if you have a list of cars with each car assigned to one or more features: A\C, color, sunroof, etc. Since each [Car] (group), can have the same [Feature], it is possible that the code Pos([VAR Max Concat Category];Previous([VAR Max Concat Category]))-3 will find the most recently added feature more than once. when that occurs, SubStr will return a shorter result than you would otherwise expect. Unfortunately, Webi doesn't have a PosFromRight() function to avoid this.
I did the following to resolve the issue in my data structure, for which this should only ever be a problem at the first position. A more robust version of this would likely require some sort of recursive check, which I don't think is possible in Webi.
=If IsNull(Previous([VAR Max Concat Category]))
Then Substr([VAR Max Concat Category];1;Length([VAR Max Concat Category])-2)
Else Substr([VAR Max Concat Category];1;
If Pos([VAR Max Concat Category];Previous([VAR Max Concat Category]))=1
And Length([VAR Max Concat Category])>Length(Previous([VAR Max Concat Category]))
Then Pos(Substr([VAR Max Concat Category];2;Length([VAR Max Concat Category]));Previous([VAR Max Concat Category]))-2
Else Pos([MaxConcatBusSeg];Previous([MaxConcatBusSeg]))-3)
(It just occured to me that using the left-side concatenation posted above might help fix this as well - adding in the same direction as the Pos() function searches. I'll look into this next week.)
It should also be noted that because of the use of Previous(), both this solution and my modification are potentially subject to problems with sorts applied to columns other than the one being grouped on = [Lines] in this case.
January 16, 2013 at 3:38 PM
Thanks for the solution.Until couple of rows the results look good and after that i get #OVERFLOW error for that specific column.Rest of the data looks good.Can you help with what might be the issue.
January 24, 2013 at 6:10 PM
Great Solution, but i am facing some difficulties while creating an [VAR Max Concat Category]. Its throwing an multivalue error. Did any one faced this issue
February 27, 2013 at 2:39 PM
Instead of using the Max function in [VAR Max Category] = Max([Category]) In ([Lines]) I propose that one consider using the Last function as displayed below.
[VAR Last Category] = Last([Category]) In ([Lines])
This allows the function to evaluate properly without regard to the sort, thus making the formula much more flexible.
April 5, 2013 at 12:12 AM
Hi,
When I am removing Category and Var Max Category , it is showing "Unavailable" under var max concat category.Any ideas why is this happening ?
May 14, 2013 at 5:12 AM
Hi Srivatsa,
I'm having issues creating the final variable. It could be due to my report being a little different from yours. I couldn't find any other solution for mine so I followed what you had here.
Everything works except the last variable, could be due to my list being exhastive and not confined to just three.
Can you please help me with the formula in a scenario like this.
Thanks
June 4, 2013 at 6:04 AM
Thanks for the solution.Until couple of rows the results look good and after that i get #OVERFLOW error for that specific column.Rest of the data looks good.Can you help with what might be the issue
July 3, 2013 at 12:24 PM
Hi Srivasta,
This is great solution, it is working somewhat for me. Variable 3 is the issue, since this works for reports with smaller data set, but in my case, after the 18th page, I am getting #OVERFLOW error since the number of characters for Variable 3 exceeds the 17,000 character limit that WEBI has....do you have any workaround for this or suggestion aside from limiting the data set more?
Also, in my example, the 'Category' can only be up to 2 values. Is there a way to tweek the formulas in this case?
December 19, 2013 at 1:07 PM
This did just what I needed. I tweaked a tiny bit to only add the comma if there is more than one value in the category. So:
[VAR Concat Category] = [Category] + If(IsNull(Previous(Self));"";(", " + Previous(Self)))
Then, the last variable doesn't have to do so much:
[VAR Category] = If(IsNull(Previous([VAR Max Concat Category]));[VAR Max Concat Category];Substr([VAR Max Concat Category];1;Pos([VAR Max Concat Category];Previous([VAR Max Concat Category]))-3))
January 2, 2014 at 8:22 AM
This is a great solution, but how would it need to be modified to account for three objects (i.e. [THINGS], [LINES], and [CATEGORY])? Thank you!
February 18, 2014 at 2:01 PM
Hi Srivasta,
the solution you have proposed is very usefull but I have some issue when the variable 3 exceeds the limit of characters.
Do you have any proposal about this issue?
Thank you very much
April 14, 2014 at 5:17 PM
Hi Srivastava,
This aricle is very useful. But thing is that I am using BO 4.1 and [VAR Concat Category] = [Category] +", "+ Previous(Self) is not giving the result the result it should have.
May 13, 2014 at 12:57 AM
Here's the set I used successfully for the same problem (with help from an overlapping thread on the scn; in particular a post by Hailu Gelan Regassa):
[VAR Last Category] = Last([Category]) In ([Lines])
[VAR Concat Category] = If(Not(IsNull(Previous(Self;([Lines]))));Previous(Self;([Lines])) + ", ") + [Category]
[VAR Full Concat Category] = [VAR Concat Category] Where ([Category]=[VAR Last Category])
That last variable is qualified as a detail of the [Lines] dimension.
September 26, 2014 at 10:36 AM
Hi,
Is there a way to change the order in which the values are displayed i.e. instead of "Shirts, Pants, Jackets" can it be "Jackets, Pants, Shirts". Thanks.
September 26, 2014 at 2:15 PM
Also, in another concatenation, it is missing the last character e.g. system shows up as "syste". Any reason why this would be happening?
Thanks.
October 10, 2014 at 12:50 PM
I'm having an issue where if multiple lines have the same VAR Category then the 1st row will show the values, but the following cells will not. How do i fix that?
October 10, 2014 at 12:51 PM
You can tweak the substr formulas in the final variable (the -1, -2) depending on how long the strings you are working with are.
December 25, 2014 at 11:57 PM
I got #OVERFLOW. Anyone solved this? Please help me.
September 27, 2015 at 11:06 PM
Works Perferctly. Thanks A lot.
October 9, 2015 at 12:22 PM
solution for #OVERFLOW problem:
create measure [test_previous FIN with current FIN] =If(Previous([Financial Number])=[Financial Number];1;0)
final concatenation variable becomes: =If([test_previous FIN with current FIN]=1) Then Previous(Self) + "; " + [dtl_ Diagnosis Description] Else [dtl_ Diagnosis Description]
All Variables used:
[Max_Dx] =Max([Diagnosis Description]) In ([Financial Number])
[Concat_Dx] =If([Test_previous FIN with current FIN]=1) Then Previous(Self) + "; " + [Diagnosis Description] Else [Diagnosis Description]
[Max_Concat Dx] =[Concat_Dx] Where([Diagnosis Description]= [Max_Dx])
[Test_previous FIN with current FIN] =If(Previous([Financial Number])=[Financial Number];1;0)
October 9, 2015 at 12:23 PM
Thank you to OP and all the other posters for helping me get this solved
October 19, 2015 at 2:17 PM
What do you mean [dtl_ Diagnosis Description]? is other variable? Because I got #multivalue
November 25, 2015 at 7:27 AM
November 25, 2015 at 7:27 AM
I tweaked the formulas to solve the overflow issue and make the list alphabetical:
[Club Max] =Max([Site].[Site - Key]) ForEach ([Article])
[Club Concat] =([Site].[Site - Key] + "; " + Previous(Self)) ForEach ([Article])
[Club List]=[Club Concat] Where ([Site].[Site - Key] = [Club Max])
There is no need for the max concat formula because the list is self-contained for each article (or Line if you use eFashion) using the "forEach" keyword.
November 26, 2015 at 1:22 AM
Hello,
is it possible to output all available Lines side by side (e.g. 3 lines = 3 columns) and all assigned categories in one common column (concatenated, sorted and separated by commas)?
Example:
| Dresses | Jackets | Leather | Boatwear, Casual dresses, Fancy fabric, ... Sweater dresses |
January 7, 2016 at 9:54 AM
Can someone please give me a Quick fix on how to handle this problem:
When the first "Control" field is null, nothing gets printed out even though values exist in the field to the right, then on the next row, the next value is shown along with EVERY one previous values in reversed order. I have no idea how to fix this.
Thanks in advance, Alex
February 23, 2016 at 5:07 PM
Dear Michael Chamberlain,
Request you to please elaborate #overflow issue with a proper example and data set as we have above. Thanks in advance !!!
February 23, 2016 at 8:04 PM
Dear Michael Chamberlain,
Request you to Please let us know how [dtl_ Diagnosis Description] came into picture as you have not specify any variable to define with this name.
October 6, 2016 at 7:42 PM
Anyone resolve #OVERFLOW problem please?
January 24, 2017 at 3:13 AM
Take a look at the November 25, 2015 at 7:27 AM answer; useful for any number of "[Lines]", short and simple, and he claims no OVERFLOW issue.
Also the May 13, 2014 at 12:57 AM by Craig McMurphy, but it seems the former is superior in simplicity and using ForEach() instead of In(), so also a lesson there.
Thanks to both and the post author!!!!! (and rest of contributors)
January 24, 2017 at 3:57 AM
Sorry if this is much easier to do and I'm overcomplicating but if we just want to use an imported Excel Column inside an InList() operator, is there any faster way than this adaptation of the November 25, 2015 at 7:27 AM code? Thank you.
For just one column to turn into ";" separated List:
[VAR Max Element] = Max([Element])In([Element])
[VAR Concat Element] = [Element] + "; " + Previous(Self)
[VAR List Element] = Last([VAR Concat Element])
February 8, 2017 at 7:48 AM
A follow up question to this post:
I used the formulas and it worked perfectly for my case. However, my next requirement was to display this [VAR Category] in the report and calculate how many times this certain set of values (or a pattern) has occurred. Moreover, I have to make a list of lines that contain the same list of categories.
In other words, if I follow your example, for CATEGORY "Evening wear, casual dresses" I have to list all lines in one cell, that have these 2 categories ONLY in the column next to it.
Any formulas used to solve this produce a #MULTIVALUE error. Even if I want to display the new CATEGORY variable on its own, it only produces #MULTIVALUE. Can you recommend any fix for this?
Thank you in advance!
April 19, 2017 at 5:38 AM
Very Nice Document.
June 22, 2017 at 8:36 AM
Thanks, this was very helpful and worked well...at least it did when I carefully followed each step.
November 8, 2017 at 2:30 AM
Hello Everyone,
Certainly a great deal of work by Srivatsa Kondapalli. I am now looking for something just opposite of this requirement where we have values in comma separated format under a single cell and we want to split this across multiple columns. I am Not looking for just replacing Commas with new line so that it splits into new line but gets displayed in single cell. but i want them to split into multiple columns along with other dimensions.
February 14, 2018 at 6:23 AM
June 10, 2018 at 1:38 AM
video sabung ayam bangkok
August 13, 2018 at 8:42 PM
August 13, 2018 at 8:42 PM
Bosan Menang tidak dibayar ? judi sabung ayam
August 16, 2018 at 1:15 PM
tarung jago
September 18, 2018 at 9:57 AM
Help needed. This method works very well. However, I am having the following two issues:
1. Getting a comma at the end of the concatenation if more than one category is concatenated.
2. When I do Sectioning, I get the error message "Unavailable"
Any idea how these issues can be rectified?
September 18, 2018 at 11:59 AM
Got the solution in a different thread. NoFilter(var_name) helps remove the UNAVAILABLE error.
September 19, 2018 at 10:59 AM
judi sabung ayam online
September 30, 2018 at 1:07 PM
Taji Ayam Sabung
December 16, 2018 at 11:25 PM
Nice Post..Thanks for Sharing..
ERP in Chennai
ERP Providers in Chennai
SAP B1 implementation
SAP s/4 Hana implementation
SAP r3 implementation
hr outsourcing
December 30, 2018 at 5:33 AM
Mau yang lebih ????? ayam tarung
December 30, 2018 at 2:48 PM
This website can live streaming , you can join at my site :
agen judi online terpercaya
Prediksi Bola
Thank you
agenpialaeropa.net
gamesonline.ga
beritasemasaterikini.com
January 24, 2019 at 4:26 PM
ANYEONGHASEYEO >>> AYAM BLACKPINK !!