topic Re: Section Access Issue in QlikView

Section Access Issue

renjithpl — Tue, 04 Dec 2012 14:42:53 GMT

Hi All,

I have my security table like below

ACCESS	NTNAME	USER_GROUP
USER	ABC	SUPERGROUP
USER	XYZ	NON_INT_GROUP
USER	PQR	INT_GROUP

and code

Section Access;

load

ACCESS, NTNAME, USER_GROUP

from .... .qvd;

Section Application;

i would like to reduce data in two levels, country and dept

so i have the country and dept table which linked with above table, assume like below;

USER_GROUP	COUNTRY	DEPT
XXXXXX	Canada	INT
XXXXXX	Canada	NON_INT
XXXXXX	MEXICO	Other

My question is,

1. when user login as PQR, he should see, all canada details including INT and NON_INT dept

2. when user login as XYZ, he should see, Canada details, but only related to NON_INT dept

I made a key, using (COUNTRY &'##' & DEPT ) as KEY and integrated with section access, but it does not work the way i want,

is there any suggestion or some simple technique to get it done..

thanks in advance

Ren

Re: Section Access Issue

Tue, 04 Dec 2012 18:54:33 GMT

you script should look like:

section access;

load * inline
[UserID, USERGROUP,user

ABC, SUPERGROUP, User
XYZ, NONINTGROUP, User

PQR, INTGROUP,User ] ;

section application;

star is *;

load * inline
[USERGROUP, COUNTRY, DEPT
SUPERGROUP, Canada, *
NONINTGROUP, Canada, NON_INT] ;

Re: Section Access Issue

hic — Wed, 05 Dec 2012 21:19:28 GMT

The star (in DEPT) will unfortunately not do what you want. You need a solution where you use Generic Keys to map your authorization.

See more on

http://community.qlik.com/blogs/qlikviewdesignblog/2012/10/02/complex-authorization
http://community.qlik.com/docs/DOC-3451

HIC

Re: Section Access Issue

Thu, 06 Dec 2012 07:54:53 GMT

you are right;)

that's becouse i didn't check this script (reload)

i hope renjithpl figured it out

Re: Section Access Issue

renjithpl — Thu, 06 Dec 2012 08:33:37 GMT

Hi Henric, would like to open the link you pasted here, but i am unable to open it.

Re: Section Access Issue

hic — Thu, 06 Dec 2012 08:47:59 GMT

You're right. Here they come again:

http://community.qlik.com/blogs/qlikviewdesignblog/2012/10/02/complex-authorization

http://community.qlik.com/docs/DOC-3451

HIC

Re: Section Access Issue

renjithpl — Thu, 06 Dec 2012 12:56:50 GMT

Thanks for the reply Henric,

I have read the doc,

But my Authorization Table has null values like below

The Dept column has null, which means, SUPERGROUP can view all the department under respective contries.

USER	USER_GROUP	COUNTRY	Dept
U1	SUPERGROUP	AE
U2	SUPERGROUP	BH
U3	SUPERGROUP	EG
U4	SUPERGROUP	IR
U5	SUPERGROUP	JO
U6	SUPERGROUP	KW
U7	SUPERGROUP	LB
U8	SUPERGROUP	OM
U9	SUPERGROUP	PK
U10	SUPERGROUP	QA

Re: Section Access Issue

hic — Thu, 06 Dec 2012 13:14:43 GMT

I think you should create a generic key like

Upper(Country) & '|' & if(Len(Trim(Dept))=0,'<ANY>',Upper(Dept)) as %COUNTRYDEPT

Then you can use an authorization bridge table to map the values of %COUNTRYDEPT to respective COUNTRY and Dept, e.g.

HIC

Re: Section Access Issue

IAMDV — Thu, 06 Dec 2012 13:27:59 GMT

Is this based on Hierarchy? What is the relationship between Country & Dept? which field sits at the most granular level? As suggested by HIC, Generic Keys with Authorization table is very powerful approach to solve this kind of requirements.

Thanks,

www.QlikShare.com

Re: Section Access Issue

renjithpl — Sat, 08 Dec 2012 12:55:58 GMT

Hi Henric sorry for the late response,

Here is my situation,

My usergroup table has

USERGROUP	COUNTRY	DEPT	KEY
SUPERGROUP	UA		UA##
SUPERGROUP	BH		BH##
SUPERGROUP	SA		SA##
GROUP2	UA	INT	UA##INT

Then comes my dimension table which has

COUNTRY	DEPT	KEY
UA	INT	UA##INT
UA	HO	UA##HO
BH	INT	BH##INT
.	.	.
.	.	.

when i tried connnecting using KEY, it maps only the GROUP2-UA-INT-UA##INT

Others it doest not show coz it does not know which row to match with, i am confused with the bridge table.

Thanks in advance..

Re: Section Access Issue

hic — Sat, 08 Dec 2012 14:32:31 GMT

The simplest way is the following:

UserGroup:

LOAD USERGROUP,

UPPER(COUNTRY & '#' & If(Len(Trim(DEPT))=0,'<ALL>',DEPT)) as KEY

FROM HierarchyInSectionAccess.xlsx (ooxml, embedded labels, table is UserGroup);

BridgeTable:

LOAD COUNTRY,

DEPT,

Upper(COUNTRY & '#' & '<ALL>') as KEY

FROM HierarchyInSectionAccess.xlsx (ooxml, embedded labels, table is Dimension);

LOAD COUNTRY,

DEPT,

UPPER(COUNTRY & '#' & DEPT) as KEY

FROM HierarchyInSectionAccess.xlsx (ooxml, embedded labels, table is Dimension);

You probably need to have the UserGroup table inside Section Access, and if you need additional tables for country and dept, you should link those to the BridgeTable

HIC

Re: Section Access Issue

renjithpl — Sun, 09 Dec 2012 06:56:12 GMT

Thanks a lot Henric, it worked sucessfully moreover i have moved one step ahead in qlikview learning.

Thank you all.