Deep hierarchy in LookML

[AndreaLooker]

Hi guys! 

I have a question if I have two tables one with the information of every id and one that determines the deep hierarchy of each id, how can I join the two of them? For example, 

Detail table: 

ID	VALUE	ID_PARENT
1	4	2
2	5	4
3	7	4
4	2	NULL

Hierarchy table: 

ID_CHILD	ID_PARENT	LEVEL
1	2	1
1	4	2
2	4	1
3	4	1

output in the explore: 

FilterID: ‘4’
ID	ID_CHILD	LEVEL	VALUE
4			22
4	2	2	9
4	1	1	4
4	3	1	7

Thank you!! I haven’t found any information about this type of problem, so any help is welcome!

[Dawid]

You can have portion of that table if you join those two views with one_to_many relationships but the total can only be achieved at the bottom of the table, like a summary, not as a row of data. 

This is because you want to have two contexts in the same output. One context is “1 row of data per child” and then “1 (extra) row as a total”. I would never keep such data in the same table. In SQL you would have to calculate two contexts separately and then UNION it. 

In Looker, the only way to do it would be to use Derived Tables and do exactly that - join different outputs into one table.