Skip to Content
0

Rows to columns in BO webi

Jun 16, 2017 at 11:42 AM

126

avatar image
Former Member

Hi All,

I have a requirement where i have to convert my rows into columns(different obviously :) )

So my data is

Id Region City

2 NAM NYC

2 NAM Washington

I have to display like this:

Id Region City1 City 2

2 NAM NYC Wash..

Is it possible to do it in webi?

10 |10000 characters needed characters left characters exceeded
* Please Login or Register to Answer, Follow or Comment.

4 Answers

Mahboob Mohammed Jun 16, 2017 at 01:26 PM
0

Hi Akshat,

Yes, its possible, try this https://blogs.sap.com/2015/08/15/string-aggregation-on-webi-report-level/. That logic fails sometimes when there is lot of data, in such cases, you can implement it in the Universe, try this blog https://irfansworld.wordpress.com/2011/09/15/how-to-display-column-values-in-a-single-cell-in-web-intelligence-using-oracle-database/

Thanks,
Mahboob Mohammed

Share
10 |10000 characters needed characters left characters exceeded
avatar image
Former Member Jun 16, 2017 at 01:49 PM
0

HI Mahboob,

Yes i was able to achieve that somehow :)

but stuck in identifying the issue to divide comma separated values into multiple columns :(
any idea on that?

eg:

column

abc,def

to, column1 column2

abc def

Show 1 Share
10 |10000 characters needed characters left characters exceeded

Use the below sample logic

FirstName = substr([Customer Name];1;pos([Customer Name];" ")

LastName = substr([Customer Name];pos([Customer Name];" ")+1;len([Customer Name])-pos([Customer Name];" ")

1
Koen Hesters Jun 19, 2017 at 02:59 PM
0

new1 =

Substring([String];0;Pos([String];",")-1)

than create new string without the first string (new1) and repeat till you have all your columns

Show 3 Share
10 |10000 characters needed characters left characters exceeded
Former Member

thanks Koen,

i have built that logic but facing issue in getting to build the first one,

ie) getting column separated values :(

so in my attached image
i have dimension Var max concat category

how to clean it ie)if you see i am getting extra ",," in my column which is causing this :(
sorry for confusing but i am badly confused as to how to resolve it

0

Hi,

for the " , , " results, I guess there are no values corresponding to your variable, so you can add a if statement if ([var] inlist (", ,";",";" , , ");"";[var])

1
Former Member

will try this and let you know :)
thanks a lot

0
Koen Hesters Jun 16, 2017 at 02:28 PM
0

Hi,

check out

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

try to set every variable as a column,

it thatis not working, you can create multiple colums with the same string in it, but you need to fetch the substrings between the ";" by getting the length of strings between the ; and each time show the string in one variable and remove the same string in the next variable and repeat that

Grtz

Koen

Show 3 Share
10 |10000 characters needed characters left characters exceeded
Former Member

Hi Koen,

Thanks for your link.

I am trying using that link but facing issues in getting the string

my output sometimes omit the initial string or the end :(

and i didn't get your second option
can you explain me that

0

hi,

cutting the string with all the "," into substrings via formula function pos, you can track the position of the "," and create substrings using that positionvalue.

Grtz

Koen

0
Former Member

issuee.png

Hi Koen,

Please have a look at the screenshot
the attribute Var Max Concat category is coming correctly

but if i try to modify it to "new"

i am getting extra "," which is creating problem :(

any idea to resovle it.

formula for new: =If(Left([VAR Max Concat Category];1)=",") Then "" Else [VAR Max Concat Category]

issuee.png (24.0 kB)
0