topic Re: Heirachy load issue with section access in QlikView

Heirachy load issue with section access

Tue, 19 Nov 2013 21:15:40 GMT

Hello QV community.

I'm having an issue with heirarchy loads combined with section access. Parent node values are being reduced out of the application, not allowing the user to click on them in a list box showing the heirarchy.

Here's an overview of the issue.

I'm loading sales data at the invoice level, each invoice has a plant code associated with it.

Example:

Plant Code	Invoice
AB	1
CW	1
PZ	2

The plant codes themselves are grouped together in a heirarchy (this is loaded in a heirarchy load)

PlantGroup	Plant Group Parent	Plant Key
Construction		Construction
Wood	Construction	ConstructionWood
Aluminum	Construction	ConstructionAluminum
Distribution		Distribution
Retail	Distribution	DistributionRetail

Then a table to tie each plant to the heirarchy

Plant Code	Plant Key
AB	ConstructionWood
CW	ConstructionAluminum
PZ	DistributionRetail

The heirarchy table created for the plant path values creates the following data (Plant Path) which can be used in a list box

Plant Path	Plant Key
Construction	-
Construction/Wood	ConstructionWood
Construction/Aluminum	ConstructionAluminum
Distribution	-
Distribution/Retail	DistributionRetail

So far, so good, all plays nicely.

Then I add section access to the application, only allowing some users to see sales data for certain plant codes.

NTNAME	Plant Code
USER1	CW
USER1	PZ
USER2	AB

Don't mind the cases, names... the section access works fine in restricting the sales data and is not a problem.

The issue comes in the heirarchy table created above (with the plant path field). If USER2 logs in for Plant Code AB the only record left in the heirarchy table is one with a plant path of Distribution/DistributionRetail, all other values includeing the PlantPath of just 'Distribution' are removed due to data reduction on load.

The list box showing plant path still displays correctly, it creates the "distribution" node, having section access turned off doesn't change how this part of the list box is displayed

Distribution

Retail

The issue is that with section access turned on and the "distribution" record being reduced the user can no longer click on the distribution node and have it select the retail node underneath, the distribution node is acutally gray even with no selections made in the application. I do realize the node has to be collapsed for it to select nodes below it, this does not correct the problem. The distribution node itself cannot be selected. Without section access and the distribution node in the table the user can select the distribution node and have it select the retail node.

Is this a QlikView bug that even though it adds the node, it obviously is not tied to the child anymore? Any thoughts on how to correct this?

Thanks!

Eric

Re: Heirachy load issue with section access

preminqlik — Wed, 20 Nov 2013 01:54:15 GMT

you can use plantkey in section access.....not plant code

Re: Heirachy load issue with section access

Wed, 20 Nov 2013 05:11:32 GMT

While not obvious in my given dataset, there can be multiple plants per plant key. For example plant CW and plant DW can both be plantkey constructionaluminum. Users may only need access to plant CW and not DW.

Re: Heirachy load issue with section access

hic — Wed, 20 Nov 2013 10:12:43 GMT

You need to have the authorization table (Section Access) reference a Tree instead of a Node. A Tree field can be created with HierarchyBelongsTo.

See more here:

http://community.qlik.com/blogs/qlikviewdesignblog/2013/11/25/hierarchy-authorization

http://community.qlik.com/blogs/qlikviewdesignblog/2013/11/11/unbalanced-n-level-hierarchies

HIC

Re: Heirachy load issue with section access

preminqlik — Sun, 24 Nov 2013 10:44:44 GMT

HI TRY THIS MAKE ANOTHER KEY IN BACK END. LIKE BELOW

PLANTCODE&'/'&PLANTPATH AS new_section_key

EG: VALUES BELOW TO ABOVE FIELD

AB/Construction/Wood

CW/Construction/Aluminum

THEN USE THIS FIELD "new_section_key " IN SECTION ACCESS

HOPE IT HELPS YOU ..

STILL HAVING PROBLEM , THEN

CREATE TRE LIST BOXES FOR SEPERATE USERS AND MAKE IT APPEARS CONDITIONALLY .

Re: Heirachy load issue with section access

Wed, 19 Feb 2014 21:41:54 GMT

While it's been a few months I've taken a couple of stabs at this and so far haven't had success. I can create the tree though the higher levels are still removed in the nodes table (therefore removing the higher level node path values).

Currently section access isn't linked to the nodes table at all, it's linked directly to the fact table (and actually uses multple fields from the fact table to create the complete section access key).

A slightly different example might better highlight the issue. I have another hierarchy linked to the fact table that has nothing to do with section access. The hierarchy is linked via a nodeid to the fact table. When I log in section access restricts which fact table records the user can see, because section access is on any records in any table linked to the fact table that do not apply are reduced, include the higher level nodes in the heirachy table.

Simple (somewhat nonsensical) example:

Hierarchy:

Animals

Big Ones

Whales

Dinosaurs

Elephants
Small Ones

Mice

Chickens

Fact Table:

Three fields:

Color Animal Count

Blue Whales 300

Blue Dinosaurs 0

Gray Mice 10000

Animal from the Fact table is tied to the NodeName of the hierarchy.

The nodepath wil be something like

Animals

Animals/Big Ones

Animals/Big Ones/Whales

Animals/Big Ones/Elephants

Animals/Big Ones/Dinosaurs

Animals/Small Ones

Animals/Small Ones/Mice

Animals/Small Ones/Chickens

If my section access restricts me to only records that have a color of blue then the nodes table will only have the following record.

Animals/Big Ones/Whales

Animals/Big Ones/Dinosaurs

The list box will still display correctly but I can no longer choose the animals level of the tree (even when the tree is collapsed) as the Animals record is no longer in the table. Any thoughts on how this can be loaded such that the Animals and Animals/Big Ones records in the nodes table can still be present?

Thanks,

Eric VanCurler

Re: Heirachy load issue with section access

hic — Mon, 24 Feb 2014 09:44:46 GMT

I think you can accomplish this by using a Tree table (as explained in the first blog post). If you store the color in the hierarchy, and not in the transaction table, you can make the reduction on Color in the Tree table. You can also use the path from the Tree table.

You may need to have a second color in the Node table also. Then you use this in list boxes and the other one for reduction.

HIC