cancel
Showing results for 
Search instead for 
Did you mean: 

Alias Question

former_member340306
Participant
0 Kudos

Hi,

I have two tables (customer and Alignment table) that are joined on Customer ID e.g.

Customer Table           Alignment Table

Customer ID   ----------- Customer ID

  Zip                               Region

                                              Zip

There is a situation where Customer ID can be Null so whenever it is null i need to use zip to zip join rather than Customer Id to customer ID join.

I created a alias of the Alignment table and joined the alias with the customer table on zip e.g.

Alignment table zip (alias)                     Customer Table           Alignment Table

    Customer ID                                       Customer ID   ----------- Customer ID

         Zip  ----------------------------------------          Zip                             Region

                                                                                                                 Zip

If i run a query on Alignment table Zip (alias) and Customer Table and another on Alignment table and Customer table and merge on report level it works fine. But that requires me to have two alignment classes in the universe (one for Zip join and other for Cmid join). I want to have one class with region defination but have the condition working such as : records where Customer Id is NOT null then get the region information using Customer ID join but records where it is NULL then use the Zip join with the Alignment table zip alias . I am trying to replicate the running of two queries on report level and merging dimension creteria on the uiverse side.

Let me know if that is possible !

Thanks,

Sheikh

Accepted Solutions (1)

Accepted Solutions (1)

former_member203850
Contributor
0 Kudos

Please try to create complex join in the universe. For example:-

Customer Table           Alignment Table

Customer ID   ----------- Customer ID

                                OR

Alignment table zip (alias)                     Customer Table         

    Customer ID                                       Customer ID  

         Zip  ----------------------------------------          Zip

Use OR clause  in join  condition..So that both should work.

Let me know how it goes.

Regards,

Pranay

former_member340306
Participant
0 Kudos

Thanks Pranay and Mantu for the helpful reply . I actually eneded up doing the samething - i have a complex join that goes like :

(Customer table.CUST_ID IS NOT NULL AND Customer TableCUST_ID=Alignment.CUST_ID)
OR
(Customer table.CUST_ID IS NULL AND Customer table.ZIP=Alignment.ZIP)

The number of rows coming out are right after the join. The only issue i am getting is that, for null CMid e.g. there are duplicate zips and when i run the report with Cmid = Null and select one zip to test for example its aggreagting the measure results of all the duplicate enteries of the zip.

So as in example:

Cmid     Zip      Amount

Null        12         10

Null        12          10

Null        13          5

Null        13          5

So in webi after the aggreagtion it shows for Zip=12 value as 20 rather than 10 and zip 13 value as 10 rather than 5. The alignment is just a dimension table with this complex join so i am not sure why this is happening.

@ Pranay:  i am going against oracle by the way. and can you give an exple of how to effectively use Coalesce statement please.

Sheikh

former_member203850
Contributor
0 Kudos

You are not able to sum up for Null values because In oracle , Null values represent missing or unknown data and it is not an integer, a character, or any other specific data type.

Secondly if you don't want NUll values in report then create a report level filter to exclude NULL.

Lets consider an example for Coalesce statement:

COALESCE returns the first non-null expression. Refer below link for more detail:


COALESCE

Regards,

Pranay

former_member4998
Active Contributor
0 Kudos

Hi


Try below

Create variable like: V_ZIP = DISTINCT(ZIP)

In the Report select Customer ID,V_ZIP, Amount

So report will show’s as

Cmid     V_ZIP     Amount

Null        12         10

Null        13          5

former_member340306
Participant
0 Kudos


Pranay, its not that i m not able to sum up the null values because they are summing up , the problem is that when CMID is null  there are duplicate values of zip coming in the report --- and those duplicate values have a measure Amount number associate to it ;;so e.g.

Cmid     Zip      Amount

Null        12         10

Null        12          10

Null        13          5

Null        13          5

Now in the webi report it showsi see one row for zip =12 e.g. but the amount is 20 rather than 10 . so it summing up the duplicate shows and iw as wondering if i can stop it from doing that. I hope that made sense.

Let me know if you have any questions

Sheikh

former_member340306
Participant
0 Kudos

i actually resolved the issue and it seems like i had it right all the way and i was missing a small condition .

But tanks for your help everyone !!!!!!

former_member340306
Participant
0 Kudos

i had to modfy the join as :

(Customer table.CUST_ID IS NOT NULL AND Customer Table.CUST_ID=Alignment.CUST_ID)
OR
(Customer table.CUST_ID IS NULL AND Alignment.Cust_ID is NULL AND Customer table.ZIP=Alignment.ZIP)

The reason was that i only wanted to get records where all customer ID's were NULL both for Alignmenet table and the Customer table, so i was missing the CUST_ID is NULL condition from the alignment table.

Answers (1)

Answers (1)

former_member190895
Active Participant
0 Kudos

As i don't know the database which you using. Below is my advise.

Instead of creating a alias table. Please try to use coalesce in the joining condition. Coalesce(CustomerID,Zip). If CustomerID is null then zip column will be use for joining condition.

Let me know if coalesce is working in joining condition.