topic Select multiple values EXCEPT one in QlikView

Select multiple values EXCEPT one

Mon, 26 Jan 2026 18:19:17 GMT

Hi All,

I have problems with my current security setup. I have a list of countries and since we added a new country recently, this new country is limited only to selected users.

ex.

USER_GROUP	COUNTRY
A	*
B	SG
B	JP
B	AM
C	JP

My country lists up to 30+. Since it's a big list, It's not really nice to list everything except the new one. Is there any solution for this?

Thanks!

Re: Select multiple values EXCEPT one

maksim_senin — Wed, 13 Apr 2016 07:58:04 GMT

Hi,

Not sure I understood your requirement, but in Section Access you can list those values which your selected users should have access so the other ones won't have an access to them.

Best regards,

Maxim

Re: Select multiple values EXCEPT one

Peter_Cammaert — Wed, 13 Apr 2016 08:13:40 GMT

Actually, Section Access (almost - there are some exceptions) follows the same rules as those that apply to your data model: associations and linking in general are only done on strictly specififed values, never on things like "all of them", or "everything except these two".

You can however simulate your own link value handling by defining an external security definition source (which is always better to make it manageable without requiring you to modify your hidden script) and adding things like include/exclude columns, or to handle self-created concepts like '*'.

The code that loads this external data and translates into a boring SA access list will become a bit more complex than a simple INLINE LOAD.

Peter

Re: Select multiple values EXCEPT one

amit_saini — Wed, 13 Apr 2016 08:18:26 GMT

Hi,

In general new row is required to assign user to different country , but what may be you can try to group these company name ....Could be create Hierarchie based on countries as regions like Asia , North America , Europe.....and than give section access on Hierarchie level.

Thanks,

Re: Select multiple values EXCEPT one

Wed, 13 Apr 2016 08:26:22 GMT

Hi Maxim,

Thanks for replying, Since users that have access to all countries I just put * to the field for them to access all countries, problem is, an additional country was recently added and selected users are only allowed to see this new country. Since the list is so long, I don't want to put it one by one per each grouping.

ex.

USERGROUP	Country
A	*

to
USERGROUP	Country
A	SG
	PH
	AM
	JP
	and so on

I don't want to list all, just because of an additional country.

Re: Select multiple values EXCEPT one

Wed, 13 Apr 2016 08:28:38 GMT

Hi AS,

Thanks for the suggestion, this suggestion is helpful unfortunately adding this new hierarchy will be needing me to update all of my applications. (more than 20 applications). Is there any easier way?

Thanks

Re: Select multiple values EXCEPT one

sujeetsingh — Wed, 13 Apr 2016 08:32:34 GMT

Use a excel sheet to have the security mapping and call it in SA script. Hence when ever you need to add the new one , just add in the sheet.

Re: Select multiple values EXCEPT one

Wed, 13 Apr 2016 08:36:04 GMT

Hi,

Actually I'm using an excel file for my security... the problem is that if I list the countries one by one by each group loading the binary files takes long.. my records of 600k jumped to 11m because of the combinations I have for my security.. I want to lessen this number if there is any solution for this.

Re: Select multiple values EXCEPT one

Peter_Cammaert — Wed, 13 Apr 2016 09:52:15 GMT

You do not have to list every country on a single line. As an example (not using an Excel but instead an INLINE LOAD, just to make my point), what about this trick?

Section Access;

LOAD * INLINE [

ACCESS, USERID, GROUP

ADMIN, SA,

USER, ME, A

USER, YOU, B

USER, HE, C

];

Section Application;

// Copying entries in this table is extremely simple

RawSAGroups:

LOAD * INLINE [

GROUP, GroupSpec

A, SG;PH;AM;JP

B, IN;SA;JP;SK;NZ;TH;LA;VN;

C, NewCountry

];

GroupTable:

LOAD GROUP, SubField(GroupSpec, ';') AS Country

RESIDENT RawSAGroup;

DROP Table RawSAGroup;:

I'm referring to my first post: use QlikView script statements to make your security easier to manage.

I don't understand why anything should grow from 600k rows to 11m rows. Can you explain why this happens?

Peter

Re: Select multiple values EXCEPT one

Peter_Cammaert — Wed, 13 Apr 2016 09:58:12 GMT

This can be made even shorter, especially if you don't like part of your SA information to reside outside of Section Access;

Section Access;

SA:

LOAD * INLINE [

ACCESS, USERID, GROUP

ADMIN, SA,

USER, ME, A

USER, YOU, B

USER, HE, C

];

// Copying entries in this table is extremely simple

LEFT JOIN(SA)

LOAD GROUP, SubField(GroupSpec, ';') AS COUNTRY INLINE [

GROUP, GroupSpec

A, SG;PH;AM;JP

B, IN;SA;JP;SK;NZ;TH;LA;VN;

C, NewCountry

];

Section Application;

// Section Access will link to a data model field called 'COUNTRY'. Add

// more fields to SA if they all link to different data model tables.

// Otherwise create a composite key.

Best,

Peter

Re: Select multiple values EXCEPT one

Thu, 14 Apr 2016 00:30:05 GMT

Hi Peter,

Thanks for the suggestion. I will try this out.. hopefully works and I'll tag your answer as correct

Thanks,

Lyn