topic Re: Query on Section Access script in QlikView

Query on Section Access script

hopkinsc — Wed, 06 Jun 2012 16:27:02 GMT

Hi all,Can anyone tell me if there is a problem when trying to do a resident load in section access please?

I am trying to do the following but it's not working.

Security:
LOAD * INLINE [
ACCESS, USERID, PASSWORD
Admin, Admin, Admin
User, User, User
];

Concatenate (Security)
LOAD
NTNAME,
ACCESS,
PROJECTCODE
FROM
C:\Subsea7\QVDev\PMSR\ChrisSecurity.xlsx
(ooxml, embedded labels, table is Sheet1);

Section Access;
Load *

Resident Security;

Section Application;

Drop Table Security;

Many thanks

Chris.

Query on Section Access script

Miguel_Angel_Baeyens — Wed, 06 Jun 2012 16:44:01 GMT

Hi Chris,

Do all the loads after the Section Access; statement. Note that all field names and values must be uppercase. Note that you needn't drop the section access table, just use the Section Application; statement.

Hope that helps.

Miguel

Query on Section Access script

hopkinsc — Wed, 06 Jun 2012 16:51:46 GMT

Hi Miguel, I am trying to create my security table first then use that security table as my section access table (If that makes sense)

My section access table comprises of an inline table and an xlsx table, i want to combine them and make a new table, then use that new table as my section access table.

I am dropping the combined INLINE AND XLSX table not the section access table (as i know this isn't displayed in my table viewer anyway)

Any ideas?

Thanks.

Query on Section Access script

rwunderlich — Wed, 06 Jun 2012 16:54:24 GMT

I disagree with Miguel's recommendation. I have run into many different problems -- slow load performance, missing data -- doing operations like JOIN or CONCATENATE in Section Application. My ROT is: build the table in Section Application, then load resident in Section Access. Just as you are doing.

As Miguel pointed out, your field values must be in upper case.

-Rob

http://robwunderlich.com

Query on Section Access script

Miguel_Angel_Baeyens — Wed, 06 Jun 2012 16:56:44 GMT

Chris,

You should combine them within the Section Access / Application statements. That will work as well, regardless how many tables you are using or how many different sources, as long as you keep the same names and values uppercase for both.

Once you have created your table, you don't have to drop it, security table is hidden for everyone (including admin) and it does not appear in the Table Viewer.

By the way, note that you are using both USERID/PASSWORD and NTNAME, and you must be carefull or you can get locked yourself out. Check this thread on how to use all three fields and the "*" sign.

Hope that makes sense.

Miguel

Query on Section Access script

hopkinsc — Wed, 06 Jun 2012 16:57:15 GMT

Hi both, I have seemed to got it working, Does this look ok to you? It seems to work.

TMPSecurity:
LOAD * INLINE [
ACCESS, USERID, PASSWORD
ADMIN, ADMIN, ADMIN
USER, USER, USER
];

Concatenate (TMPSecurity)
LOAD
NTNAME,
ACCESS,
PROJECTCODE
FROM
C:\Subsea7\QVDev\PMSR\ChrisSecurity.xlsx
(ooxml, embedded labels, table is Sheet1);

Store TMPSecurity into Security.txt (txt);

Drop Table TMPSecurity;

Section Access;
LOAD ACCESS,
USERID,
PASSWORD,
NTNAME,
PROJECTCODE
FROM
C:\Subsea7\Security.txt
(txt, utf8, embedded labels, delimiter is ',', msq);

Section Application;

Re: Query on Section Access script

Miguel_Angel_Baeyens — Wed, 06 Jun 2012 17:12:13 GMT

Hi Chris,

Yes, that should work, taking into account what I mentioned above in regards to mix USERID and NTNAME.

As opposed to Rob, I have run into problems specially using Publisher, when using RESIDENT loads for Section Access. That should work, anyway, there's nothing wrong in using a RESIDENT with section access. However I do have run into issues when using JOIN, in many many cases, and not only in section access.

As most of the questions, it's all about recommendations or suggestions, rather than the answer.

Hope that helps somehow.

Miguel

Query on Section Access script

hopkinsc — Thu, 07 Jun 2012 07:41:14 GMT

Thanks both. What i have done works, alothough it does cause a security risk as i am creating a txt file with PASSWORDS in it.Do you know if there is a way olf qlikview deleting the txt file after it has loaded it in. Maybe a command at the end of the script which will just delete the file.
Thanks

Re: Query on Section Access script

Miguel_Angel_Baeyens — Thu, 07 Jun 2012 07:53:42 GMT

Hi,

If that works to you as Rob suggests above, just don't create any file, or do a QVD instead. Just do the table, concatenate, join and do what you need, load resident in the Section Access script then drop the table. I'd give a try to not create any additional file as he mentions.

Hope that helps.

Miguel

Query on Section Access script

hopkinsc — Thu, 07 Jun 2012 07:56:46 GMT

Rob says to build the file in section application, then load resident in section access. I don't completely understand what is meant, i thought section access had to come before section application?

Re: Query on Section Access script

Miguel_Angel_Baeyens — Thu, 07 Jun 2012 08:39:57 GMT

Hi,

Section Application means anywhere but in the Section Access itself. Section Application is where the "normal" script is. Note that in QlikView it does not matter necessarily where to load a table, because all links will be created at the end of the load script between tables that share a field name.

So your script should look like

Security:

LOAD ...

CONCATENATE ...

Section Access;

LOAD *

RESIDENT Security;

Section Application;

// Tables here of normal load script

Hope that helps.

Miguel

Query on Section Access script

hopkinsc — Thu, 07 Jun 2012 08:58:45 GMT

Hi Miguel,

So are you saying that the followinbg should work?

TMPSecurity:
LOAD * INLINE [
ACCESS, USERID, PASSWORD, NTNAME
Admin, Admin, Admin,*
User, User, User,*
];

Concatenate (TMPSecurity)
LOAD
NTNAME,
ACCESS,
PROJECTCODE
FROM
C:\Subsea7\QVDev\PMSR\ChrisSecurity.xlsx
(ooxml, embedded labels, table is Sheet1);

Section Access;
LOAD *
Resident TMPSecurity;

Section Application;

When i reload this and attempt to reopen the QVW it states i that my access is denied..

I have set up my NTNAME as admin and i should have access to 1 project code out of 3.

Re: Query on Section Access script

Miguel_Angel_Baeyens — Thu, 07 Jun 2012 09:12:17 GMT

Hi,

I think you need to change this:

Concatenate (TMPSecurity)

LOAD

'*' AS USERID, // This line is new

'*' AS PASSWORD, // this line is new

NTNAME,

ACCESS,

PROJECTCODE

FROM

C:\Subsea7\QVDev\PMSR\ChrisSecurity.xlsx

(ooxml, embedded labels, table is Sheet1);

Add the field PROJECTCODE with "*" in the INLINE table as well.

Hope that helps.

Miguel

Query on Section Access script

hopkinsc — Thu, 07 Jun 2012 09:23:41 GMT

Sorry to be a pain, i cannot get it to work, it keeps locking me out.

This is my new script..

TMPSecurity:
LOAD * INLINE [
ACCESS, USERID, PASSWORD, NTNAME, PROJECTCODE
Admin, Admin, Admin,*,*
User, User, User,*,*
];

Concatenate (TMPSecurity)
LOAD
'*' as USERID,
'*' AS PASSWORD,
NTNAME,
ACCESS,
PROJECTCODE
FROM
C:\Subsea7\QVDev\PMSR\ChrisSecurity.xlsx
(ooxml, embedded labels, table is Sheet1);

Section Access;
LOAD *
Resident TMPSecurity;

Section Application;

Re: Query on Section Access script

Miguel_Angel_Baeyens — Thu, 07 Jun 2012 09:39:56 GMT

Chris,

Are fields and values uppercase?

TMPSecurity:
LOAD * INLINE [
ACCESS, USERID, PASSWORD, NTNAME, PROJECTCODE
ADMIN, ADMIN, ADMIN,*,*
USER, USER, USER,*,*
];

Concatenate (TMPSecurity)
LOAD 
'*' AS USERID,
'*' AS PASSWORD,
Upper(NTNAME) AS NTNAME,
Upper(ACCESS) AS ACCESS,
Upper(PROJECTCODE) AS PROJECTCODE
FROM
C:\Subsea7\QVDev\PMSR\ChrisSecurity.xlsx
(ooxml, embedded labels, table is Sheet1);

If that does not work, please post the excel and QVW file (remove all sensitive data and charts but keep the section access script) so we can check further. You can either comment the Section Access line just to check whether the table is or is not concatenating as expected.

Hope that helps.

Miguel

Query on Section Access script

hopkinsc — Thu, 07 Jun 2012 09:44:31 GMT

Hi Miguel,

My NTNAME was lowercase!! I have just changed it and it now seems to be working. Thanks for your help, Sorry to be a pain!

Many thnaks

Chris.

Query on Section Access script

Miguel_Angel_Baeyens — Thu, 07 Jun 2012 09:49:57 GMT

Hi Chris,

Nevermind, that usually happens. I'm glad to help but please mark helpful and correct answers as such, so the next user can find it out easily.

Thanks!

Miguel