Need logic on join in order to maintain dimension granularity / Drill Down .

[alina_qlik]

Hello Experts ,

Even though i am new to Qlik but , i have learnt a lot from community about join , yet somehow not sure about below problem statement . Sorry in advance , if i have asked something "no brainier" . 

I have two tables , using which i need a resulting table which depicts dimension drill down .

Table 1 :

Client	ProductType	INCOME	Region
Merck	Cash	200	US
Merck	CashX	100	US
Merck	AUS Val	100	US
Merck	Local Draws	25	US
Merck	Bonds	75	US
Merck	Share	50	US
Merck	Debentures	50	US
INV	Cash	300	GB
INV	CashX	120	GB
INV	AUS Val	180	GB
INV	Local Draws	60	GB
INV	Bonds	60	GB
INV	Share	180	GB

Table 2

Product Levl 1	Product Levl 2	3Product Levl 1
Cash	CashX	Local Draws
Cash	CashX	Bonds
Cash	AUS Val	Share
Cash	AUS Val	Debentures

Resulting Table 

Corp	Region	Product	Product Levl 2	3Product Levl 1	INCOME
Merck	US	Cash	CashX	Local Draws	25
Bonds	75
AUS Val	Share	50
Debentures	50
INV	GB	Cash	CashX	Local Draws	60
Bonds	60
AUS Val	Share	180
Debentures	-

I am not getting to the point , which join should be used or how to be used ?

Thank You Qlikxperts !!

[Lauri]

I am having trouble understanding the structure of your tables. It looks like Table 1 has multiple levels of product in the ProductType field. Do rows 2 and 3 for Merck (CashX and AUS Value), at 100 each, add up to the row 1 (at 200)?

Is "Local Draws" a subset of "CashX" which is a subset of "Cash"?

Why does your resulting table have ProductType values in the Corp column? Or did the formatting get messed up when you pasted it into this question?

[alina_qlik]

Table 1 :

Client	ProductType	INCOME	Region
Merck	Cash	200	US
Merck	CashX	100	US
Merck	AUS Val	100	US
Merck	Local Draws	25	US
Merck	Bonds	75	US
Merck	Share	50	US
Merck	Debentures	50	US
INV	Cash	300	GB
INV	CashX	120	GB
INV	AUS Val	180	GB
INV	Local Draws	60	GB
INV	Bonds	60	GB
INV	Share	180	GB

Table 2

Product Levl 1	Product Levl 2	3Product Levl 1
Cash	CashX	Local Draws
Cash	CashX	Bonds
Cash	AUS Val	Share
Cash	AUS Val	Debentures

Resulting Table 

Corp	Region	Product	Product Levl 2	3Product Levl 1	INCOME
Merck	US	Cash	CashX	Local Draws	25
Merck	US	Cash	CashX	Bonds	75
Merck	US	Cash	AUS Val	Share	50
Merck	US	Cash	AUS Val	Debentures	50
INV	GB	Cash	CashX	Local Draws	60
INV	GB	Cash	CashX	Bonds	60
INV	GB	Cash	AUS Val	Share	180
INV	GB	Cash	AUS Val	Debentures

[alina_qlik]

After going through the community , i think it is Hierarchy related problem , than simple join .. 

And not sure how to use hierarchy here   

[Lauri]

Hi, Hierarchy (or HierarchyBelongsTo) could be a solution, but your situation doesn't require it. (I wasn't aware of those functions, so thanks for teaching me something.)

I put your data into two sheets in Excel and did an Inner Join:

[Result]:
LOAD
[Client] as Corp,
[Region],
[ProductType] as "3Product Levl 1",
[INCOME]
FROM [lib://AttachedFiles/qliklearning.xlsx]
(ooxml, embedded labels, table is Table1);

Inner Join
LOAD
[Product Levl 1] as Product,
[Product Levl 2],
[3Product Levl 1]
FROM [lib://AttachedFiles/qliklearning.xlsx]
(ooxml, embedded labels, table is Table2);

I got the result you want (except I don't get the last row with no INCOME). Hopefully it works with a bigger data set as well.

[alina_qlik]

Hey Laurischarf ,

Glad to know that , you learnt about Hierarchy .  If by any chance can you explain me  Hierarchy or HierarchyBelongsTo

using above problem statement . 

Thank you for your effort.

[Lauri]

If you look at the Qlik Help, it shows how Hierarchy puts the parent for every child into a new column. 

https://help.qlik.com/en-US/sense/February2019/Subsystems/Hub/Content/Sense_Hub/Scripting/ScriptPref...

In your data, it would put 'Cash' into a new column next to 'CashX' as its parent. And it would put 'CashX' next 'AUS Val' as its parent. Etc.

But Hierarchy requires ID numbers for the parent and for the child to indicate the relationships. You should be able to calculate them from Table 2, add them to Table 1, and then use Hierarchy.

[alina_qlik]

Ah , getting confused . Would you mind to share a qvf with hierarchy as you explained above ? 

[Lauri]

The example Qlik Help provides:

Hierarchy(NodeID, ParentID, NodeName, ParentName, NodeName, PathName, '\', Depth) LOAD * inline [

NodeID, ParentID, NodeName
1, 4, London
2, 3, Munich
3, 5, Germany
4, 5, UK
5, , Europe
];

A similar statement with your data might be:

Hierarchy(NodeID, ParentID, NodeName, ParentName, NodeName, PathName, '\', Depth) LOAD * inline [

NodeID, ParentID, NodeName
1, 3, Bonds
2, 3, Local Draws
3, 4, CashX
4, , Cash
];

This says that Cash is the parent of CashX, which is the parent of Local Draws and Bonds.

So you need to somehow create NodeID and ParentID before you can run the Hierarchy load statement.