Split data from one column to two column

building reports desktopintelligence
1

Hi,

I am using BO XI R2.
I am using UNION to join two tables (A & B) because there is no any common field to join these two tables.
select
a.PI_date,a.ID
from a
where a.ser_tye <> 1
union
select b.GI_date,b.Infra_ID
from b
where Product_cat = ‘PP’

At report level it shows only tow columns PI_date and ID. It upend all the records in two column. I want to split the records from the ID column.
One for ID data and other one for Infra_ID data.

Data is displayed in the report is :
ID PI_date
CRQ000000001418 1/8/2009
CRQ000000001462 1/4/2009
CRQ000000001464 1/3/2009
CRQ000000001465 1/3/2009
CRQ000000001466 1/4/2009
INC000000569486 1/1/2009
INC000000569557 1/2/2009
INC000000578787 1/4/2009
INC000000583985 1/6/2009
INC000000584738 1/6/2009

I want to split CRQ% data in one column and INC% data in another column.
Anyone have idea, how to split this data in tow columns?

Awaiting your favorable reply

At report level I cannot use like function to split these records.

assist (BOB member since 2006-04-17)

2

If you have access to designer try using a Like operator in the designer level.May be it works for you.

ajitha :india: (BOB member since 2008-07-28)

3

Its a free hand SQL report.

assist (BOB member since 2006-04-17)

4

Hi Assist,

If I understood your question properly

Can’t you create couple of variables using substr() some thing like

CRQ_ID = if substr(<ID>,1,3) = 'CRQ' then <ID> else ''

and

INC_ID = if substr(<ID>,1,3) = 'INC' then <ID> else ''

and get them in two different columns and try to filter on nulls or something like that

cyberdude :india: (BOB member since 2007-02-05)

5

Hi,

The issue is resoulved by using Match fuction.
=If Match( ,“CRQ*”) Then “Planned Events” Else “UnPlanned Events”

=Count() Where (< Plan Unplan Bucket>=“Planned Events”)

Thank you very much for your kind support.

assist (BOB member since 2006-04-17)