Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Announcements
Save the Date: QlikWorld Online, June 24-25, 2020. Free global virtual event for data integration and data analytic gurus. Register Today
Highlighted
New Contributor III

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


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 :

ClientProductTypeINCOMERegion
MerckCash200US
MerckCashX100US
MerckAUS Val100US
MerckLocal Draws25US
MerckBonds75US
MerckShare50US
MerckDebentures50US
INVCash300GB
INVCashX120GB
INVAUS Val180GB
INVLocal Draws60GB
INVBonds60GB
INVShare180GB


Table 2

Product Levl 1Product Levl 23Product Levl 1
CashCashXLocal Draws
CashCashXBonds
CashAUS ValShare
CashAUS ValDebentures

 

Resulting Table 

CorpRegionProductProduct Levl 23Product Levl 1INCOME
MerckUSCashCashXLocal Draws25
Bonds75
AUS ValShare50
Debentures50
INVGBCashCashXLocal Draws60
Bonds60
AUS ValShare180
Debentures-


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

Thank You Qlikxperts !!

8 Replies
Highlighted
Contributor III

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

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?

Highlighted
New Contributor III

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

 

Table 1 :

ClientProductTypeINCOMERegion
MerckCash200US
MerckCashX100US
MerckAUS Val100US
MerckLocal Draws25US
MerckBonds75US
MerckShare50US
MerckDebentures50US
INVCash300GB
INVCashX120GB
INVAUS Val180GB
INVLocal Draws60GB
INVBonds60GB
INVShare180GB

 

Table 2

Product Levl 1Product Levl 23Product Levl 1
CashCashXLocal Draws
CashCashXBonds
CashAUS ValShare
CashAUS ValDebentures

 

Resulting Table 

CorpRegionProductProduct Levl 23Product Levl 1INCOME
MerckUSCashCashXLocal Draws25
MerckUSCashCashXBonds75
MerckUSCashAUS ValShare50
MerckUSCashAUS ValDebentures50
INVGBCashCashXLocal Draws60
INVGBCashCashXBonds60
INVGBCashAUS ValShare180
INVGBCashAUS ValDebentures
Highlighted
New Contributor III

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

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

And not sure how to use hierarchy here   

Highlighted
Contributor III

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

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.

Highlighted
New Contributor III

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

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.

Highlighted
Contributor III

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

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.

Highlighted
New Contributor III

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

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

Highlighted
Contributor III

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

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.