Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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 !!
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?
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 |
After going through the community , i think it is Hierarchy related problem , than simple join ..
And not sure how to use hierarchy here
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.
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.
If you look at the Qlik Help, it shows how Hierarchy puts the parent for every child into a new column.
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.
Ah , getting confused . Would you mind to share a qvf with hierarchy as you explained above ?
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.