Section Access - Full access using Blank or *

[drjoffily]

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?

[sushil353]

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

[drjoffily]

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?

[sushil353]

Hi,

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

[hic · Accepted solution]

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

[sujeetsingh]

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

[drjoffily]

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

[drjoffily]

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