Split a string value separated by / to multiple columns

building reports webintelligence
1

I have a column in a BO report that has the Location full name in it.
Example of Location Full Name-
/NA/USA/ORD/CS-B343/Z4/
/AP/Australia/ADL/

This columns is understood as-
Region- NA
Country- USA
Location- ORD
Room- CS-B343
Cabinet- Z4

This columns is pulled from the database as is and as defined in the Universe. As you can see that the character count varies too.

Now in the BO report we need to split this column and have it split to different columns Region , Country , Location , Room , Cabinet
Quite much similar to the text to columns in Excel which is “/” delimited.

Can anyone suggest a way how to achieve this in a Webi report?
We will not be making any changes at the designer level therefore looking at option to do this at the webi report stage.

Thanks.

tekkieuntracked :us: (BOB member since 2010-08-18)

2

I think the replace function would work for you - you could try changing the “/” to a carriage return or CR/LF (chr10 / chr13) and allow your field to stretch… some fonts will display a box for the special character, may have to experiment.

Good luck!
B

bdouglas :switzerland: (BOB member since 2002-08-29)

3

Thanks for your reply.
We are using the web version only for report design, I believe I can use carriage return or CR/LF (chr10 / chr13) only in the thick client?
Also we will not be able to make any changes to the Universe.

tekkieuntracked :us: (BOB member since 2010-08-18)

4

you can create a variable and do that in webi - i believe it will work in web…

all you are really trying to do is trigger the word wrap in a field on the report, something that you won’t see…

B

bdouglas :switzerland: (BOB member since 2002-08-29)

5

Thanks, I was able to get the Region , Country , Location , Room , Cabinet in new line using the Replace and Char(13)
Formula- =Replace([Location Full Name];“/”; Char(13))

However now how do I get these Region , Country , Location , Room , Cabinet in different columns? Just like the Texts to Columns in Excel.

Any thoughts.

Split a string value separated by forward slash to multiple columns.JPG

tekkieuntracked :us: (BOB member since 2010-08-18)

6

You can do this with POS and SUBSTR. I would begin by working out the position of all of the Backslashes in the string:

FirstBackslash:

=Pos([LocationName];"/")

SecondBackslash:

=Pos(Substr([LocationName];[FirstBackSlash]+1;100);"/")+[FirstBackSlash]

ThirdBackslash:

=Pos(Substr([LocationName];[SecondBackSlash]+1;100);"/")+[SecondBackSlash]

…etc.

You can then derive the columnar contents using SUBSTR:

Region:

=Substr([LocationName];[FirstBackSlash]+1;[SecondBackSlash]-([FirstBackSlash]+1))

Country:

=Substr([LocationName];[SecondBackSlash]+1;[ThirdBackSlash]-([SecondBackSlash]+1))

You see the pattern.

HTH

NMG

mcnelson :uk: (BOB member since 2008-10-09)

7

Thanks mcnelson, this worked!!

Thank you all for the replies…

tekkieuntracked :us: (BOB member since 2010-08-18)