Storing Hierarchy Tables

[Ori_Hait]

Hi everyone,

i've got a table of parent child relationships which i use the hierarchy function on to flatten it.

parent_child:

  sql
  SELECT ole.item_id AS "NodeId",
         ole.PARENT AS "ParentId",
         ole."name" AS "NodeName",
         ole.assignment_group as assignment_group,
         agd."name" as group_name 
  FROM tfm.org_logical_entity ole
  left join tfm.assignment_group_details agd
  on agd.snow_sys_id = ole.assignment_group
  ;

Hierarchy(NodeId, ParentId, NodeName, ParentName, NodeName, pathname, '\', Depth) 
load * resident parent_child;

drop table parent_child;

Problem is that I cant seem to find a way to refer to the columns created by that function in order to use them for script aggregations because qlik sense creates it with the "-ExpandedNodes" suffix to its name and the load script is having a hard time with the first dash on the left.

Thought about storing the table first but again, for the reason specified before, qlik doesn't react to well to using that character and I'm getting the following error 

The error occurred here:
store parent_child>>>>>>-<<<<<<ExpandedNodes into lib://Main_Connection/pacificlife_sr/ETL/DEV/parent_child-ExpandedNodes.qvd]

Has anyone encountered this error before? how did you overcome it?

Thanks!

Ori.

[chriscammers · Accepted solution]

Try explicitly naming your table and then referring to that name.

parent_child:

  sql
  SELECT ole.item_id AS "NodeId",
         ole.PARENT AS "ParentId",
         ole."name" AS "NodeName",
         ole.assignment_group as assignment_group,
         agd."name" as group_name 
  FROM tfm.org_logical_entity ole
  left join tfm.assignment_group_details agd
  on agd.snow_sys_id = ole.assignment_group
  ;

[FlatHierarchy]:
Hierarchy(NodeId, ParentId, NodeName, ParentName, NodeName, pathname, '\', Depth) 
load * resident parent_child;

drop table parent_child;
Store FlatHierarchy into [file.qvd](qvd);

[rwunderlich]

If the table referenced in the store statement contains special characters, it should be enclosed in [] or "".

Store [parent_child-ExpandedNodes] into ...

-Rob

[Ori_Hait]

So embarrassing, thanks 🙂