云存储_极盗者百度云_12月免费

产品 虚拟云 浏览

小编: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

云存储_极盗者百度云_12月免费

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

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.

Cheers,

Rogerio

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!!

Best Regards,

Suman

HI Suman,

thank you so much for your comment!

ROgerio

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!

BR

Michael

Thank you very much Rogerio

,云数据平台,数据库,云之讯,试用云,云数据库排行

文章来源:www.vmchk.com

 
你可能喜欢的: