topic Re: Section Access Wlidcards in QlikView

Section Access Wlidcards

steal999 — Wed, 25 Nov 2020 16:16:04 GMT

Hi,

Please help! I'm new to QlikView and i'm currently looking at how Section Access works.

What i'd like to do is use wildcards at different intervals of a field.

I have a code field which consists of alpha & numeric characters:

AB00001

AB00002

AB00003

AC00001

AC00002

AC00003

BA00001

BA00002

BA00003

CB00001

CB00002

etc.

How I would like to set the section access:

Access / NTName / Code

USER / User1 / A* - This user would see all codes beginning with A

USER / User2 / *B* - This user would see all codes with the second letter being B

USER / User3 / AC* - This user would see all codes beginning AC

USER / User4 / AB00001 - This user will only see the code AB00001

The reason I want to set it up like this is because we have hundreds of codes and 1 user will need access to a lot of them and the next user will only need 1 or 2.

Also, it makes maintenance easier and if new codes are set up, we don't have to worry about adding it someone's access if they all ready have that range of codes.

Hope that makes sense and is it possible?

Cheers

Ste

Re: Section Access Wlidcards

martinpohl — Sat, 23 Dec 2017 10:15:58 GMT

section access is working on correct datas, not with wildcards.

but you can create your section access tables with the values from your tables.

let vUsers = noofrows('useracces')-1; //how many users

for vUser = 0 to $(vUsers) //create loop

let vUseractual = peek('User',$(vUser),'$(useraccess'); // peek user from table

let vAcessactual= peek('Code',$(vUser),'$(useraccess');

// peek Code from table

load

'ADMIN' as ACCESS,

$(vUseractual) as NTNAME,

Code

resident your datas

where User = $(vUseractual)

and Code like '$(vAccessactual)';

next // do next

Re: Section Access Wlidcards

steal999 — Wed, 03 Jan 2018 13:46:43 GMT

Thanks Martin,

I've given this a go but I can't get it to work, like I said, I'm new to QlikView so I'm probably missing something obvious.

I get the error below:

Syntax error

load

'ADMIN' as ACCESS,

as >>>>>>NTNAME<<<<<<,

Code

resident useraccess

where User =

and Code like ''

The code i'm using is:

useraccess:

LOAD ACCESS,

NTNAME,

CODE

FROM

[Test Wildcard.xlsx]

(ooxml, embedded labels, table is Sheet1);

let vUsers = noofrows('useraccess')-1; //how many users

for vUser = 0 to $(vUsers) //create loop

let vUseractual = peek(NTNAME,$(vUser),'$(useraccess'); // peek user from table

let vAccessactual= peek(CODE,$(vUser),'$(useraccess'); // peek Code from table

load

'ADMIN' as ACCESS,

$(vUseractual) as NTNAME,

Code

resident useraccess

where User = $(vUseractual)

and Code like '$(vAccessactual)';

next // do next

The Test Wildcard spreadsheet basically holds the 3 columns as below:

Access / NTName / Code

USER / User1 / A*

USER / User2 / *B*

USER / User3 / AC*

USER / User4 / AB00001

Any help would be really appriciated

Ste

Re: Section Access Wlidcards

martinpohl — Wed, 03 Jan 2018 21:12:07 GMT

oh, sorry, a correction is needed:

change

let vUseractual = peek(NTNAME,$(vUser),'$(useraccess'); // peek user from table

let vAccessactual= peek(CODE,$(vUser),'$(useraccess'); // peek Code from table

let vUseractual = peek(NTNAME,$(vUser),'useraccess'); // peek user from table

let vAccessactual= peek(CODE,$(vUser),'useraccess'); // peek Code from table

Re: Section Access Wlidcards

steal999 — Thu, 04 Jan 2018 11:10:13 GMT

Hi Martin,

Tried that as well and still getting the same error, not sure why but those 2 variables are not populating.

I changed let to set to make sure the vUser variable number changes for each user and that seems to be working fine.

Below is the coding again in case I've done something stupid as I've tried several changes:

USERACCESS:

LOAD ACCESS,

NTNAME,

CODE

FROM

[Test Wildcard.xlsx]

(ooxml, embedded labels, table is Sheet1);

let vUsers = noofrows('USERACCESS')-1;

for vUser = 0 to $(vUsers)

let vUseractual = peek(NTNAME,$(vUser),'USERACCESS');

let vAccessactual = peek(CODE,$(vUser),'USERACCESS');

SECTION_ACCESS:

load

'ADMIN' as ACCESS,

$(vUseractual) as NTNAME,

CODE

Resident USERACCESS

where NTNAME = $(vUseractual)

and CODE like '$(vAccessactual)';

next

Cheers

Ste

Re: Section Access Wlidcards

Miguel_Angel_Baeyens — Thu, 04 Jan 2018 15:43:58 GMT

Name of field in Peek() should also be between single quotes:

LET vUseractual = Peek('NTNAME', $(vUser), 'USERACCESS');

And then on the LOAD use again to specify the variable has a literal value

LOAD

...

'$(vUseractual)' AS NTNAME

...

You can go to the script editor, click on debug, then on the line of the LET click on the left hand side, where the line number is to add a breakpoint and run step by step to know which assignment is not working.

However, that code seems that you are doing exactly the same as in the first load. since you cannot use wildcards, you should loop through all values in CODE, which is the field you use for reduction, unless of course "*B*" is a value stored into the CODE field in the data model.

Re: Section Access Wlidcards

steal999 — Fri, 05 Jan 2018 13:46:20 GMT

Thanks Miguel,

Did that and still got an error but then I added quotes to

where NTNAME = '$(vUseractual)'

Seems to work now but it's confused me as the result I get is:

ACCESS	NTNAME	CODE
ADMIN	USER1	A*
ADMIN	USER2	B
ADMIN	USER3	AC*
ADMIN	USER4	AB00001
USER	USER1	A*
USER	USER2	B
USER	USER3	AC*
USER	USER4	AB00001
		AB00002
		AB00003
		AC00001
		AC00002
		AC00003
		BA00001
		BA00002
		BA00003
		CB00001
		CB00002
		CB00003

You are right, '*B*' does not exist in the Code field and it's now duplicating each user for ADMIN & USER access types???

Plus guys, i'm not quite sure how this will help my situation, maybe i'm missing the point but I would have expected to have seen something more like the result below so I could add section access to the 'ACCESS_CODE' field:

ACCESS	NTNAME	CODE	ACCESS_CODE
USER	USER1	AB00001	A*
USER	USER1	AB00002	A*
USER	USER1	AB00003	A*
USER	USER1	AC00001	A*
USER	USER1	AC00002	A*
USER	USER1	AC00003	A*
USER	USER2	AB00001	B
USER	USER2	AB00002	B
USER	USER2	AB00003	B
USER	USER2	CB00001	B
USER	USER2	CB00002	B
USER	USER2	CB00003	B
USER	USER3	AC00001	AC*
USER	USER3	AC00002	AC*
USER	USER3	AC00003	AC*
USER	USER4	AB00001	AB00001

I've obviously got a lot learn.

My latest coding is:

CODE_TABLE:

LOAD * INLINE [

CODE

AB00001

AB00002

AB00003

AC00001

AC00002

AC00003

BA00001

BA00002

BA00003

CB00001

CB00002

CB00003

];

USERACCESS:

LOAD * INLINE [

ACCESS, NTNAME, CODE

USER, USER1, A*

USER, USER2, *B*

USER, USER3, AC*

USER, USER4, AB00001

];

let vUsers = noofrows('CODE_TABLE')-1;

for vUser = 0 to $(vUsers)

let vUseractual = peek('NTNAME',$(vUser),'USERACCESS');

let vAccessactual = peek('CODE',$(vUser),'USERACCESS');

SECTION_ACCESS:

load

'ADMIN' as ACCESS,

'$(vUseractual)' as NTNAME,

CODE

Resident USERACCESS

where NTNAME = '$(vUseractual)'

and CODE like '$(vAccessactual)';

next

Thanks

Ste