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 :
DP1
DP2
|
My task here is to bring all data in a single table, like :
Student code | Student Name | Course Code | Mother's Name |
B1 | Roger | Course1 | Jeanne |
B1 | Roger | Course2 | Jeanne |
B1 | Roger | Course3 | Jeanne |
B2 | Nadia | Course1 | Maria |
B3 | Cecilia | Nadia |
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.
Cheers,
Rogerio
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
User | Count |
---|---|
37 | |
10 | |
6 | |
4 | |
3 | |
3 | |
3 | |
2 | |
2 | |
2 |