split column data

building reports webintelligence
1

hi all,

Data in one of my output field is in below format and I want to split it and put it into different columns. Is it possible?

LimitAB5000-WAllocatedAmount0.000000040000000.0000000LastReviewDate24/06/2011 00:50:38:00030/06/2011 00:40:30:000

Also, is there any way to replace ?

FYI, I tried Replace([DIMENSION];Char(10);";") to replace but it didn’t work. I also tried Replace([DIMENSION];Char(13);";") and char(254)

Regards

Aussie2582 (BOB member since 2008-04-16)

2

is this coming in excel or you are getting no infoview like that only?

aniketp :uk: (BOB member since 2007-10-05)

3

i am getting it in infoview. I also copied the whole column as text and in NOTEPAD++ where I see it as ? . I tried to replace CHAR(63) in infoview but no luck

Aussie2582 (BOB member since 2008-04-16)

4

CHAR(63) is an ASCII for that?

And did you try to export to pdf format and see if it is coming in that or not? And are you going to use this as an excel?

aniketp :uk: (BOB member since 2007-10-05)

5

Hi…

Please try using following replace formula…


=Replace([Test];"cntrl char";";")

copy and paste the control character at string to replace position.

And the text you are trying to replace is Ctrl char ETX of Ascii 003.

Please find the image showing the result after applying above formula…
Result.JPG

lnarayanan86 :india: (BOB member since 2009-08-05)

6

i tried it before and I tried it again. no luck. Please see the attached screenshot.

Any other Ideas?
screenshot.doc (31.0 KB)

Aussie2582 (BOB member since 2008-04-16)

7

You can try forever, You can’t make BO do this. You have dirty data; BO will report is as such. You can substitute characters forever, but you can’t make it show up in different columns.

Steve Krandel :us: (BOB member since 2002-06-25)

8

thankyou but I cannot replace ? can you please give me some ideas

Aussie2582 (BOB member since 2008-04-16)

9

Aussie from where you are getting that symbol, is it there in the db, if Yes can you replace that there itself , I guess that will be best solution else I don’t think report level solution is going to help you

And also on what basis you need to split the data in columns ? Are you looking for SubStr()?

aniketp :uk: (BOB member since 2007-10-05)

10

thanks everyone.

i tried Replace(Char(3);" ") and it works. thanks lnarayanan86

Aussie2582 (BOB member since 2008-04-16)

11

:+1: , Finally you got the solution. And what about splitting of data in different columns? :slight_smile:

aniketp :uk: (BOB member since 2007-10-05)