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.
How can I get these two expression correctly?
Thank you in advance.
