Hi,
I have two tables: the first one is call CATEGORY and essentially represents a 5 levels tree of categories; the second one is called REQUEST_DETAIL and is a list of records each one categorized using the categories defined in the CATEGORY table.
I loaded the CATEGORY table in qlikview using the HIERARCHY function, so I have the structure of the categories split in different levels and the depth of each category.
When I select depth = 1 (all the roots of the tree) I would like to count in the second table not only the number of records corresponding to the selected categories but even the number of records correspoding to the sons of the selected roots.
Same thing if i select depth = 2 (in this case all roots will be excluded) and so on.
It's 2 days that i'm trying to find a solution to this problem, can anyone help me?
Here' s an extract of the tables:
CATEGORY
| CATEGORY_ID | CATEGORY_NAME | FATHER_ID |
|---|
| 1 | A | |
| 2 | B | 1 |
| 3 | C | 1 |
| 4 | D | 3 |
| 5 | E | 4 |
| 6 | F | 4 |
DEPTH TREE STRUCTURE
1 A
2 B C
3 D
4 F E
REQUEST_DETAIL
| ID | CATEGORY_ID |
|---|
| 1 | 1 |
| 2 | 2 |
| 3 | 3 |
| 4 | 2 |
| 5 | 5 |
| 6 | 6 |
| 7 | 5 |
| 8 | 5 |
So, counting the number of records with the same category i have:
A = 1 time
B = 2
C = 1
D = 0
E = 3
F = 1
If I select DEPTH = 1 I shoud see:
A = 1+2+1+0+3+1
If I select DEPTH = 2:
B = 2 and C = 1+0+3+1
DEPTH = 3:
D = 0+3+1
DEPTH = 4:
E = 3 and F = 1
Thanks in advance.
