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 :

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

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 :

 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
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]:
[Client] as Corp,
[Region],
[ProductType] as "3Product Levl 1",
[INCOME]
FROM [lib://AttachedFiles/qliklearning.xlsx]
(ooxml, embedded labels, table is Table1);

Inner Join
[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 .

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.