Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

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

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
Highlighted
Valued Contributor

Re: Select Hierarchy parents are greyed out while children are not

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
Highlighted
Valued Contributor

Re: Select Hierarchy parents are greyed out while children are not

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

Highlighted
Contributor

Re: Select Hierarchy parents are greyed out while children are not

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);

Highlighted
Contributor

Re: Select Hierarchy parents are greyed out while children are not

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?

Highlighted
Contributor

Re: Select Hierarchy parents are greyed out while children are not

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