Showing posts with label category. Show all posts
Showing posts with label category. Show all posts

Wednesday, March 21, 2012

Joining Two Measure Groups

Hey all,

Our product structure is:

Category

Class

Subclass

Item

We have 1 measure group by category, and another measure group by item. So we are forced to join by Category. Which works fine except for whereever we join on Category some reason the measures get rolled down all the way to the Item level.

Example:

Category Sales

00001 50.75

Product Measure Group 1 Measure Group 2
Category 00001 50.75 50.75

Class 00002 0.00 50.75

Subclass 00003 0.00 50.75

Item 00004 0.00 50.75

It's important to note Measure Group 1 is ONLY at the category level. Some reason it rolls down when to joni to Measure Group 2? Any ideas how to prevent this?

Thanks a lot.


Hello! I have written a short post about this problem on my blog: http://thomasianalytics.spaces.live.com/blog/cns!B6B6A40B93AE1393!381.entry

My example is from the Adventure Works project but the way to solve this should work for your problem also.

HTH

Thomas Ivarsson

|||

Perfect - thanks!

Joining tables and entries in one to permute with number of categories in other


I have a table of raw data that I can sort by say Division and Category. I have 11 Divisions and 4 Categories but this might not be reflected by the data in the table because there might be no entires for every combination.

However I want to generate output that presents all sets of Divisions grouped by Category, that is 11 entries for Category1, 11 for Category2, and so on.

Right now I'm using a subquery to create a table that contains all the Divisions I'm interested and I right join that with my source table. But it doesn't work, I still get a variable number of divisions for each category depending on whether that division-category link exists in my main table.

What's there to do?

To give a little more detail:

Given Category1, Category2, Category3, and Division1, Division3

I would like to get

Division1 Category1
Division2 Category1
Division1 Category2
Division2 Category2
Division1 Category3
Division2 Category3

What I'm getting now is

Division1 Category1
Division2 Category1
Division2 Category2
Division1 Category3

Depending on what entries actually exist in the source table.
|||With SQL 2005, use a CROSS JOIN.|||I just realized how misleading my post was...I may have oversimplified (hopefully I just think I did) the problem.

The thing is, I need to join the entries in that table with the other one based on a key. For example

Table1:

Key Col2 Col3 ... DivisionID Col4 Col5 etc
1 a a 1 a a
2 b b 2 a a
3 c c 5 a a
4 d d 4 a a
Table 2:

DivisionID DivisionName
1 Name1
2 Name2
3 Name3

Now I need to achieve that functionality in the light of me wanting to extract and aggregate some data from table 1.

Would Cross joining between Category and Division and Right joining the result with my original table do the trick?
|||

A CROSS JOIN will provide you all possible combinations from the two tables joined (Division and Category).

You could then join that resultset with another table (JOIN type as necessary -EQUI, LEFT, RIGHT).

|||

I'm still not clear.

Is there a reason a LEFT join on DivisionID will not work?

Adamus

|||It doesn't work because it doesn't generate the layout I desire (check my post above). In terms of correctness yeah, it works, but in terms of layout it doesn't because it only generates a subset of the entries I need.

Whether there is an entry to link the key of that table to the Division X and Category Y or not I want in my output to have the entry

Division Category SomeDataColumn
X Y 0
|||

So I understand or still misunderstand:

Table1:

Key Col2 Col3 ... DivisionID Col4 Col5 etc
1 a a 1 a a
2 b b 2 a a
3 c c 5 a a
4 d d 4 a a

Table 2:
DivisionID DivisionName
1 Name1
2 Name2
3 Name3

If Key 1 has 5 divisions, and 5 divisions is the most divisions any key can have, you want all keys to reflect having 5 divisions whether there's information associated or not?

Adamus

|||Yes, that is correct.

I think I may have figured out a way to make that happen. I performed a cross join between the tables 1 & 2 to get the sets I was interested to and right joined that to my raw date table based on the Category criteria. I seem to be getting the layout I need but I still have to determine if all the information that's supposed to get in, really does get in.

Thanks for your input everyone.

Joining tables

Can someone please advise how i join the following two tables:
I want to left join on dt,Category and subcategory from Table 1
Table 1:
SELECT TOP 100 PERCENT Y, M, Category, SubCategory,dt
FROM (SELECT dbo.tblCalendar_jp.dt, dbo.tblCalendar_jp.Y,
dbo.tblCalendar_jp.M, dbo.TblRetrospectiveDiary.SubCategory,
dbo.TblRetrospectiveDiary.Category
FROM dbo.tblCalendar_jp CROSS JOIN
dbo.TblRetrospectiveDiary
WHERE (dbo.tblCalendar_jp.FY = 2005)) calrd
GROUP BY Y, M, Category, SubCategory
ORDER BY Y, M
Table 2:
SELECT Category, SubCategory, dt, [Time]
FROM (SELECT Category, SubCategory, LEFT(DateFrom, 11) AS dt,
DateTo, DATEDIFF(mi, DateFrom, DateTo) AS [Time]
FROM dbo.TblRetrospectiveDiary) rd
Thanks in anticipationjust a hint
SELECT TOP 100 PERCENT Y, M, Category, SubCategory,dt
FROM (SELECT dbo.tblCalendar_jp.dt, dbo.tblCalendar_jp.Y,
dbo.tblCalendar_jp.M, dbo.TblRetrospectiveDiary.SubCategory,
dbo.TblRetrospectiveDiary.Category
FROM dbo.tblCalendar_jp CROSS JOIN
dbo.TblRetrospectiveDiary
-->> stop here
join -- this is the join
(
SELECT Category, SubCategory, dt, [Time]
FROM (SELECT Category, SubCategory, LEFT(DateFrom, 11) AS dt,
DateTo, DATEDIFF(mi, DateFrom, DateTo) AS [Time]
FROM dbo.TblRetrospectiveDiary) rd
) as a
on
-- join statement here use "a" as alias of 2nd query
--continueing statement from Q1
WHERE (dbo.tblCalendar_jp.FY = 2005)) calrd
GROUP BY Y, M, Category, SubCategory --include the other fields in the
select list
ORDER BY Y, M
thanks,
Jose de Jesus Jr. Mcp,Mcdba
Data Architect
Sykes Asia (Manila philippines)
MCP #2324787
"Keith Rudd" wrote:

> Can someone please advise how i join the following two tables:
> I want to left join on dt,Category and subcategory from Table 1
> Table 1:
> SELECT TOP 100 PERCENT Y, M, Category, SubCategory,dt
> FROM (SELECT dbo.tblCalendar_jp.dt, dbo.tblCalendar_jp.Y,
> dbo.tblCalendar_jp.M, dbo.TblRetrospectiveDiary.SubCategory,
> dbo.TblRetrospectiveDiary.Ca
tegory
> FROM dbo.tblCalendar_jp CROSS JOIN
> dbo.TblRetrospectiveDiary
> WHERE (dbo.tblCalendar_jp.FY = 2005)) calrd
> GROUP BY Y, M, Category, SubCategory
> ORDER BY Y, M
>
> Table 2:
> SELECT Category, SubCategory, dt, [Time]
> FROM (SELECT Category, SubCategory, LEFT(DateFrom, 11) AS dt,
> DateTo, DATEDIFF(mi, DateFrom, DateTo) AS [Time]
> FROM dbo.TblRetrospectiveDiary) rd
> Thanks in anticipation
>
>