BusinessObjects Board

Alias Table issue

building semantic layer
#1

Hi,
I have a requirment where a policy number and both primary and spousal names should be displayed on the same row.

I tried doing it using the merge variables and it worked but the customer needs it on the Universe layer.

So i created an alias table with the names and I am dropping it in the universe and creating variables in webi like where relationship is not primary for the alias table but i don’t see any changes. Any idea what must be done ir what i have done is wrong.

Just a n example of what i am trying to achieve

Ticket Number Primary Owner Secondary Owner

12345 A

12345 B

Instead of this i need the values like below

Ticket Number Primary Owner Secondary Owner

12345 A B[/code]

ash0550 :canada: (BOB member since 2011-09-12)

#2

In short this is what i am looking for

having 2 columns from same table with different where conditions

ash0550 :canada: (BOB member since 2011-09-12)

#3

Yes, that should be possible. It would help if you were to provide more details about what you’ve done – the joins between the tables and the conditions.

joepeters :us: (BOB member since 2002-08-29)

#4

Hi Joe,

So i created 2 alias tables for the which contains the client names and details and I joined them with 2 different alias tables which have the relationship information

Now I am bringing in the client name alias table in the query along with the relationship alias tables and creating 2 variables where I am using relationship = primary for one and relationship=spouse for another . When I do this i get multiple values and now i am using the previosu function to filter out the multiple values and only display the valid information which has both the primary and spousal information in the same line

Well this solved the issue , I think there might be a better solution than this

ash0550 :canada: (BOB member since 2011-09-12)

#5

You can achieve this without universe modification, by creating two queries in WebI.

In the first query, apply a condition for “relationship = self”. The second query should be identical exception the condition is “relationship = spouse”.

In the report, you’ll need to merge on the “ticket number” object, but not “client name”. If you have Auto-Merge Dimensions on, then WebI will automatically merge Client Name since it exists in both queries. Un-merge them if this happens.

Before you can include the new Client Name objects together, you’ll need to create a detail variable for each, else you’ll get a #DATASYNC error. Create a new variable, set it to Detail, and simply set its definition to one of the two client name objects (ex. =[Query 1].[Client Name]. For Associated Dimension, picked the merged “Ticket Number” object.

With all of the above done, you should now be able to include Ticket Number, and the two Client Name objects together in one block, which will display the “self” and spouse names together.

joepeters :us: (BOB member since 2002-08-29)

#6

I first used the merge dimension feature to solve the issue. The team didnt want it , they wanted changes only on the universe. Hence the other route

ash0550 :canada: (BOB member since 2011-09-12)

#7

If you want a universe solution, then you may need derived tables.

Assuming your data looks like this:
FACT


TICKET_NUMBER 
12345

RELATIONSHIP
TICKET_NUMBER CLIENT_ID RELATIONSHIP
12345                 1               Self
12345                 2               Spouse

CLIENT
CLIENT_ID CLIENT_NAME
1                J. Smith
2                M. Smith

You would then create a derived table over the relationship and client tables:


select 
    rel.ticket_id,
    max((case when relationship_type = 'Self'
          then client_name
          end)) self_name,
    max((case when relationship_type = 'Spouse'
          then client_name
          end)) spouse_name
from
    relationship rel
        join client 
            and rel.client_id = self.client_id
group by
  rel.ticket_id

joepeters :us: (BOB member since 2002-08-29)

#8

Thanks Joe.

ash0550 :canada: (BOB member since 2011-09-12)