topic Re: Section Access - Full access using Blank or * in QlikView

Section Access - Full access using Blank or *

drjoffily — Thu, 02 May 2013 06:20:10 GMT

Hi,

I'm using Section Access to control the level of access in my dashboard and I'm doing it reading from a table in Excel, since the Section Access tool imbedded in QEMC is very basic and couldn't control the level I need to implement.

I got to a point where I need to add every value in a field separated by commas and some of the fields I'm using to reduce my data have about 30 options.

Please see the example below where I reduce data based on the field SA REGION.

What I want to achieve is to be able to use '*' or ' ' or 'ALL' instead of having to list all the options like in the example below where I have to list all regions (USER | GLOBAL\B | Australia,Asia,Europe,Americas,New Zealand,Pacific)

ACCESS	NTNAME	SA REGION
ADMIN	GLOBAL\A	Australia,Asia,Europe,Americas,New Zealand,Pacific
USER	GLOBAL\B	Australia,Asia,Europe,Americas,New Zealand,Pacific
USER	GLOBAL\C	Australia
USER	GLOBAL\D	Asia,Americas
USER	GLOBAL\E	Australia
USER	GLOBAL\F	Australia,Asia,Europe,Americas,New Zealand,Pacific
USER	GLOBAL\G	Australia,Asia,Europe,Americas,New Zealand,Pacific

This is how I'm using it in my scripts...

Section Access;

[SA TABLE]:
LOAD ACCESS,
NTNAME,
SubField(REGION, ',') as [SA REGION]
FROM
$(vSecurity)
$(vTabletype);

Section Application;

[Region]:
LOAD UPPER(NTNAME),
SubField(REGION, ',') as [SA REGION]
FROM
$(vSecurity)
$(vTabletype_Region);

Does anyone have any idea how I can do it?

Re: Section Access - Full access using Blank or *

sushil353 — Thu, 02 May 2013 06:38:31 GMT

Hi,

I have a sample query.. hope you will get some idea from that.

Section Access;

LOAD * INLINE [

ACCESS, USERID, PASSWORD,%COUNTRY

admin,admin,admin,*

user,sushil,user,ASIA

user,challa,user,US

];

Section Application;

LOAD * INLINE [

country,%COUNTRY

INDIA,ASIA

US,US

AFRICA,ASIA

];

tab1:

LOAD * Inline

[

country,customer,sales

INDIA,a,30

INDIA,b,30

INDIA,c,40

US,a,50

US,b,60

US,c,70

AFRICA,a,20

AFRICA,b,10

AFRICA,c,30

];

if your issue is not resolved then please update the sample data.

--sushil

Re: Section Access - Full access using Blank or *

drjoffily — Thu, 02 May 2013 06:53:14 GMT

Thanks for your quick response Sushil.

The scripts and table I attached are just a portion of the script/table in my SA

I actually have multiple tabs in Excel to control multiple fields/Tabs so I couldn't move to a simple INLINE table. And I don't want to have to modify my scripts everytime I need to provide access to a new user. And the change management process in my company is really long. I prefer controling my SA via spreadsheet where I can change any time I want.

Is there any way to achieve it using Excel spreadsheets instead of INLINE tables?

Re: Section Access - Full access using Blank or *

sushil353 — Thu, 02 May 2013 07:33:29 GMT

Hi,

yes it is possible... please post the sample aap for better understanding.

Re: Section Access - Full access using Blank or *

hic — Thu, 02 May 2013 08:54:18 GMT

I would define a variable for all regions and use that in Section Access, i.e. something along the lines of

AllRegions:

Load Concat(distinct Region, ',') as AllRegions From Regions ;

Let vAllRegions = peek('AllRegions',0,'AllRegions');

Drop Table AllRegions;

Section Access;

[SA TABLE]:

LOAD

ACCESS,

NTNAME,

SubField('$(vAllRegions)', ',') as [SA REGION]

FROM AuthorizationsTable;

HIC

Re: Section Access - Full access using Blank or *

sujeetsingh — Thu, 02 May 2013 11:17:40 GMT

henric sir you each reply give a basic way of doing complex thing .it resolved a issue for mee.

Re: Section Access - Full access using Blank or *

drjoffily — Thu, 02 May 2013 23:56:58 GMT

Hi sushil,

Thanks again for your response.

I'm afraid the application contains sensitive information and it'd take too long to change it in a way I could post it to you.

Nevertheless, I think the info I provided above gives a good idea what I'm trying to achieve. I just want to replace the whole string by a '*' or a ' ' when someone has full access to that particular data and find a way for Qlikview to read the '*' and translate it as access to all regions. Please see example of what I want to have in my spreadsheet.

ACCESS	NTNAME	SA REGION
ADMIN	GLOBAL\A	*
USER	GLOBAL\B	*
USER	GLOBAL\C	Australia
USER	GLOBAL\D	Asia,Americas
USER	GLOBAL\E	Australia
USER	GLOBAL\F	*
USER	GLOBAL\G	*

Thanks

Re: Section Access - Full access using Blank or *

drjoffily — Fri, 03 May 2013 01:25:38 GMT

Thanks Henric,

You got me on the right track.

The only minor change I did in your solution to suit my needs was as below:

Section Access;

[SA TABLE]:

LOAD

ACCESS,

NTNAME,

If(REGION = '*', SubField('$(vAllRegions)', ',') , SubField(REGION, ',')) as [SA REGION]

FROM AuthorizationsTable;

Thanks a lot