小编：Today I would like to show a way to merge two queries with a 1xN relationship. WEBI is good on merging queries ( DP1 and DP2 ) in which for one row of DP1 there’s, at most, one row in DP2 and vice versa. When you come to a situation i
Today I would like to show a way to merge two queries with a 1xN relationship.
WEBI is good on merging queries ( DP1 and DP2 ) in which for one row of DP1 there’s, at most, one row in DP2 and vice versa.
When you come to a situation in which for each row in DP1 you can have more than one row in DP2 it’s a problem.
Let’s take an example. Suppose you have a query with students I will call it DP1. In DP1 you got: Student code, Student Name, Mother’s name and a measure (meas1). A second query DP 2 contains the course in which students are enrolled, DP2 has Student code, Student Name, Course code and a measure meas2.
Each student can be enrolled in zero or more courses
I will populate this queries with the following data :
My task here is to bring all data in a single table, like :
The data is on two MsExcel sheets , so, firstly, I need to import it to WEBI Rich Client.
After importing the sheets, that’s how my data looks like.
I will merge the two queries on Student name and Student code, which are the common objects.
A table with the merged Student name and Student code will present the data from both data providers.
To show the not merged dimensions coming from DP1 and DP2, I must create detail variables. Let’s do it with Mother’s name
For Mother’s name detail I choose , for the associated dimension the merged Student code. Dropping it on the table containing merged Student code and merged Student name :
The challenge now is to bring course code into the table. If I create a detail variable over course code , this will generate a #MULTIVALUE error since , for example, Student "Rogerio" is enrolled in 3 courses.
So, we need to , force the relationship to be reflected on the table. We can achieve it by dropping the Course code dimension in the table, which will result in the following table :
The problem here is that Student "Cecil" that isn’t enrolled in any course won’t show up.
In order to show "Cecil", select the table , right click and go to "Format table" and check "Show rows with empty dimension values"
Now the table shows the value for "Cecil"shows up
As a rule of thumb , when trying to merge DP’s with a 1xN relationship :
1 – Merge the common fields;
2 – Use the dimension coming from the N side query;
3 – Create detail variables from the 1 side query for each dimension needed with associated dimension equal the merged dimension;
4 – Check "Show rows with empty dimension values"on Table formatting for each table using dimensions coming from both queries.
Any comments will be much appreciated.
Hi Rogerio Plank ,
Your blog is very good and informative . I just loved the way you have demonstrated with sample data. I have got better understanding on Merging concept now.
Expecting more blogs from you!!
thank you so much for your comment!
Hi Rogerio Plank,
thank you very much, I will try this and maybe it is the answer of a problem I struggle with for weeks, now.
Best thing is that the student taking 3 courses at once (while Cecil takes none) is also named Rogerio, what a coincidence!
Thank you very much Rogerio，云数据平台，数据库，云之讯，试用云，云数据库排行