Aggregating by condition

Wed, 13 Jan 2016 16:00:43 GMT

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	3000	8
Pediatrics	4000	5 (3+2)
Gynecology	4000	5 (3+2)
Oncology	6500	8
Geriatrics	6500	8
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.

topic Aggregating by condition in QlikView

Aggregating by condition