Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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?
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.
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;
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:
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:
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);
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?
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 '|'