Qlik Community

Ask a Question

New to Qlik Sense

If you’re new to Qlik Sense, start with this Discussion Board and get up-to-speed quickly.

Announcements
Our May 2021 end-to-end product release from Data Integration to Data Analytics is out! READ DETAILS
cancel
Showing results for 
Search instead for 
Did you mean: 
alina_qlik
Contributor III
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
Lauri
Specialist
Specialist

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
Contributor III
Contributor III
Author

 

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
alina_qlik
Contributor III
Contributor III
Author

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

And not sure how to use hierarchy here   

Lauri
Specialist
Specialist

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
Contributor III
Contributor III
Author

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
Specialist
Specialist

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
Contributor III
Contributor III
Author

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

Lauri
Specialist
Specialist

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.