topic Re: Storing Hierarchy Tables in App Development

Storing Hierarchy Tables

Ori_Hait — Mon, 08 Jul 2024 14:34:52 GMT

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.

Re: Storing Hierarchy Tables

chriscammers — Mon, 08 Jul 2024 14:58:53 GMT

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

Re: Storing Hierarchy Tables

rwunderlich — Mon, 08 Jul 2024 18:19:52 GMT

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

Store [parent_child-ExpandedNodes] into ...

-Rob

Re: Storing Hierarchy Tables

Ori_Hait — Tue, 09 Jul 2024 06:08:50 GMT

So embarrassing, thanks 🙂