on 09-17-2014 6:14 PM
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
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
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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
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:
Regards,
Pranay
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
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.
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.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
User | Count |
---|---|
84 | |
24 | |
11 | |
9 | |
7 | |
6 | |
5 | |
5 | |
5 | |
4 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.