Two weeks ago I create a discussion about a aggregation for a dimension not included in fact table.
Today, I am searching a similar solution for a dataset with more complexity.
I have a Fact Table that concatenating three different data sources.
Using the same example from my previous post:
Fact Table:
FACT TABLE
Id_Person
Specialty
Age
Gender
Cost
Affected
1
Pediatrics
3
Male
1000
1
2
1
Female
1
3
Emergencies
14
Male
2000
1
4
33
Male
1
5
Gynecology
20
Female
3000
1
6
45
Male
1
7
Oncology
67
Male
6000
1
8
Geriatrics
74
Female
500
1
Fact table contain a concatenation of 3 differents data sources. Only one of them has Specialty (and Cost) information.
Dimension:
DIMENSION TABLE Specialty
Dimension
Condition
Pediatrics
<15
Gynecology
>15 & Female
Geriatrics
>65
Oncology
ALL
Emergencies
ALL
I need a table with two expression that calculates Cost by Specialty and Affected People by Specialty (It is not a count of fact table, it is a record counter filtered by condition). So:
Expected
Specialty
Sum(Cost)
Sum(Affected)
Pediatrics
1000
3
Emergencies
2000
8
Gynecology
3000
2
Oncology
6000
8
Geriatrics
500
2
Pediatrics
3000
8
Emergencies
Pediatrics
4000
5 (3+2)
Gynecology
Oncology
6500
8
Geriatrics
Pediatrics
4500
6 (3+2+2-1, this one is for a woman in Gyn & Ger)
Gynecology
Geriatrics
I try to get this result with a Sum(Cost) and pick(match(Affected)) but pick & match doesn't work fine in a table with Specialty.
