Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
richard_cioci
Creator
Creator

Select Hierarchy parents are greyed out while children are not

Hi Everyone!

I've recently forayed into hierarchies in QV in the hopes of a fancy expenses dashboard for work. (I believe) I've setup the hierarchy properly using the hierarchy load and hierarchy belongs to functions of QV. I have a field called AcctStructure used to display the treeview of the Account hierarchy. And while aggregation seems to be working correctly, I have a few cases where the parent is greyed out as having no linked data, and yet when expanded it has children who are white and some who are grey. This means that if I click and hold across one of these parents for a selection, then it will not select it or the children which have associated data. What could be the reason for this?

Capture.PNG

The above image shows Acct Parent SAFE1E as being grey and having no associated data when collapsed (except for the dot to the right indicating one of children have data)

The below image shows the children of SAFE1E, and they even all have data associated.

Capture.PNG

But other aggregate parents also have children with data and children without, so why are these particular parents being greyed out?

Code Below:

SET ThousandSep=',';

SET DecimalSep='.';

SET MoneyThousandSep=',';

SET MoneyDecimalSep='.';

SET MoneyFormat='$#,##0.00;($#,##0.00)';

SET TimeFormat='h:mm:ss TT';

SET DateFormat='D/M/YYYY';

SET TimestampFormat='D/M/YYYY h:mm:ss[.fff] TT';

SET MonthNames='Jan;Feb;Mar;Apr;May;Jun;Jul;Aug;Sep;Oct;Nov;Dec';

SET DayNames='Mon;Tue;Wed;Thu;Fri;Sat;Sun';

FactTable:

LOAD

  @1 as Entity,

  upper(@1) as ENTITY,

  @2 as [Entity FU],

  @3 as PC,

  @4 as FU,

  @5 as [PnL Line],

  @6 as CC,

    @7 as Acct,

    @8 as Version,

    @9 as Month,

    @10 as Data

FROM

[--Removed--.cma]

(txt, codepage is 1252, no labels, delimiter is ',', msq);

//Hierarchy prep and removal of any acct mapping who's top parent is not SAFE Total Expenses using the Check field

HierarchyPrep:

Load *

where Check = 1;

LOAD AutoNumber(@1) as NodeID,

  AutoNumber(@2) as ParentID,

  @1 as Node,

  @2 as Parent,

  @3,

  @4,

  @5,

  @6,

  @7,

  @8,

  @9,

  @10,

  @11,

  if(right(@1&@2&@3&@4&@5&@6&@7&@8&@9&@10&@11,30)='SAFE            Total Expenses',1,0) as Check

FROM

[---Removed--.Cma]

(txt, codepage is 1252, no labels, delimiter is ',', msq);

//Removal of additional parent nodes for the SAFE Total Expenses check

Drop fields @4,  @5,  @6,  @7,  @8,  @9,  @10,  @11;

//Hierarchy Prep

Hierarchy

(NodeID, ParentID, Node,ParentName,NodeName,AcctStructure,)

Load NodeID,

Node as NodeName,

ParentID,

Node,

Parent Resident HierarchyPrep;

//Proper mapping of nodes to all parents to enable proper aggregation

HierarchyBelongsTo(NodeID_H,ParentID_H,Acct,NodeID, Ancestor, Depth)

Load NodeID as NodeID_H,

ParentID as ParentID_H,

Node as Acct

Resident HierarchyPrep;

//Drop to avoid syn keys and cleanup model

Drop table HierarchyPrep;

1 Solution

Accepted Solutions
Sergey_Shuklin
Specialist
Specialist

Hello, Richard!

Using HierarchyBelongsTo() you've created a hierarchy TreeBridge, but not the hierarchy tree.

To create a tree and remove greys create once more Hierarchy() load after the HierarchyBelongsTo() with tree nodes.

This link shows the way:

Unbalanced, n-level hierarchies

View solution in original post

4 Replies
Sergey_Shuklin
Specialist
Specialist

Hello, Richard!

Using HierarchyBelongsTo() you've created a hierarchy TreeBridge, but not the hierarchy tree.

To create a tree and remove greys create once more Hierarchy() load after the HierarchyBelongsTo() with tree nodes.

This link shows the way:

Unbalanced, n-level hierarchies

richard_cioci
Creator
Creator
Author

Ok So I did that and am still having the same issue.... thoughts?

SET ThousandSep=',';
SET DecimalSep='.';
SET MoneyThousandSep=',';
SET MoneyDecimalSep='.';
SET MoneyFormat='$#,##0.00;($#,##0.00)';
SET TimeFormat='h:mm:ss TT';
SET DateFormat='D/M/YYYY';
SET TimestampFormat='D/M/YYYY h:mm:ss[.fff] TT';
SET MonthNames='Jan;Feb;Mar;Apr;May;Jun;Jul;Aug;Sep;Oct;Nov;Dec';
SET DayNames='Mon;Tue;Wed;Thu;Fri;Sat;Sun';

//Hierarchy prep and removal of any acct mapping who's top parent is not SAFE Total Expenses
HierarchyPrep:
Load *
where Check = 1;
LOAD AutoNumber(@1) as NodeID,
AutoNumber(@2) as ParentID,
@1 as Node,
@2 as Parent,
if(right(@1&@2&@3&@4&@5&@6&@7&@8&@9&@10&@11,30)='SAFE Total Expenses',1,0) as Check
FROM
[\removed.Cma]
(
txt, codepage is 1252, no labels, delimiter is ',', msq);

//Hierarchy Prep
Hierarchy
(
NodeID, ParentID, Node,NodeParent,Node,AcctStructure, '/', NodeDepth)
Load NodeID,
Node,
ParentID
Resident HierarchyPrep;

//Proper mapping of nodes to all parents to enable proper aggregation
[TreeBridge]:
HierarchyBelongsTo(NodeID,ParentID,NodeName,TreeID, TreeName)
Load NodeID,
ParentID,
Node as NodeName
Resident HierarchyPrep;

[Trees]:
Hierarchy (TreeID, TreeParentID, Tree)
Load NodeID as TreeID,
ParentID as TreeParentID,
Node as Tree
Resident HierarchyPrep;

 
//Drop to avoid syn keys and cleanup model
Drop table HierarchyPrep;
 
FactTable:
LOAD
@1 as Entity,
upper(@1) as ENTITY,
@2 as [Entity FU],
@3 as PC,
@4 as FU,
@5 as [PnL Line],
@6 as CC,
@7 as Acct,
@8 as Version,
@9 as Month,
@10 as Data,
AutoNumber(@7) as NodeID
FROM
[removed.cma]
(
txt, codepage is 1252, no labels, delimiter is ',', msq);

richard_cioci
Creator
Creator
Author

And am I understanding correctly that I can't use that tree view to select the accts? I have to use the TreeName field in a separate list box? Won't that be confusing to the user?

richard_cioci
Creator
Creator
Author

Found my issue! My accounts had forward slashes in the names, which means the treeview was creating a non existing account in the tree view, so obviously no hierarchy mapping existed for it either. Will switch separator to '|'