Skip to main content
Announcements
Qlik Community Office Hours, March 20th. Former Talend Community users, ask your questions live. SIGN UP
cancel
Showing results for 
Search instead for 
Did you mean: 
hopkinsc
Partner - Specialist III
Partner - Specialist III

Query on Section Access script

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.

1 Solution

Accepted Solutions
Miguel_Angel_Baeyens

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

View solution in original post

16 Replies
Miguel_Angel_Baeyens

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

hopkinsc
Partner - Specialist III
Partner - Specialist III
Author

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.

rwunderlich
Partner Ambassador/MVP
Partner Ambassador/MVP

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

Miguel_Angel_Baeyens

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

hopkinsc
Partner - Specialist III
Partner - Specialist III
Author

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;

.

Miguel_Angel_Baeyens

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

hopkinsc
Partner - Specialist III
Partner - Specialist III
Author

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

Miguel_Angel_Baeyens

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

hopkinsc
Partner - Specialist III
Partner - Specialist III
Author

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?