Can't we use Section Access without Keyword UPPER CASE

[sangeeth8787]

Hi,

I am not sure whether its qlik behaviour or am I missing, I have 80 million records security qvd, 

In order to achieve optimized qvd performance, 

Step1)

StarSecurity:

LOAD UPPER(USERID) AS USERID,
UPPER(PASSWORD) AS PASSWORD,
UPPER(ACCESS) AS ACCESS,
UPPER(SERIAL) AS SERIAL,
UPPER(QV_SEC) AS QV_SEC,

FROM xyz.qvd(qvd);

store StarSecurity into localdrive.qvd(qvd)  

Case 1 ) -- Under Assumption of StarSecurity is all security details with UPPER CASE, 

Section Access; //I am not exclusively not mention UPPER case here 

LOAD USERID,
PASSWORD,
ACCESS,
SERIAL,
QV_SEC,

FROM
StarSecurity.qvd
(qvd);

SECTION APPLICATION;

This file is not accepting password and hence the file is lockep up

Case2)

SECTION Access;

LOAD UPPER(USERID) AS USERID,
UPPER(PASSWORD) AS PASSWORD,
UPPER(ACCESS) AS ACCESS,
UPPER(SERIAL) AS SERIAL,
UPPER(QV_SEC) AS QV_SEC 

From
StarSecurity.qvd
(qvd);

SECTION Application;

If i amend above QVD FILE with UPPER CASE, then I am able to enter userid and password and file is accessible. but the issue is the load is not optimized.

Is it a bug of qlikview that users have to use UPPERCASE and can't obtain optimized qvd load for section access, or am I missing something?

Thank you

[marcus_malinow]

Your section access load can't be an optimised load (unless something has changed recently). A trick to get around this is to add a dummy WHERE clause to your load, such as 'WHERE 1=1'. This will break qvd optimisation.

The field names in your section access script should be upper case, however the values don't need to be upper case.

Hope this helps.

Marcus

[sangeeth8787]

Hi,

My field names are always in UPPER CASE, but unless I specify keyword UPPER  keyword for all fields the section access is not working.

SECTION ACCESS;

UPPER(USERID) -- WORKING

SECTION ACCESS;

USERID  ---- Not working

[Brett_Bleess]

You will find the 'requirement' for uppercase here:

https://help.qlik.com/en-US/qlikview/November2017/Subsystems/Client/Content/Security.htm

Scroll down to the Section Access System Fields section, then past the table and the last paragraph below that states the following:

All the fields listed in Load or Select statements in the section access must be written in UPPER CASE. Any field name containing lower case letters in the database should be converted to upper case using the upper function before being read by the Load or Select statement.

Hopefully this is what you needed.  I will also be relocating your post to the appropriate product forum as well.

Regards,
Brett

[sangeeth8787]

Hi,

When I was about to implement Security in QlikView,  I am newbie to QV, I have read the same documentation using Qlik Help documentation, As per the documentation they mentioned the Fields which we are mentioning should be in UPPER CASE, if they aren't in upper case, use upper case function, but its not mandatory to use UPPER() function as long as the fields are in already in UPPER CASE

All the fields listed in Load or Select statements in the section access must be written in UPPER CASE.

The example they mentioned

section access;

load * inline [

ACCESS, USERID,REDUCTION, OMIT //No Upper function here, just fields are written in upper case.

ADMIN, ADMIN,*,

USER, A,1

USER, B, 2,NUM

USER, C, 3, ALPHA

];

[Brett_Bleess]

Actually, in looking up and article to attach for you, I found the actual culprit as well! 🙂  Take a read through the following article, when loading QVD into Section Access things must be expanded, that is why things are not working with the UPPER.  

The workaround here if you want to load optimized would be to load the QVD into a Temp table, then do a resident load off the Temp table into your Section Access table, I believe that should get you what you want if you do not want to have the optimization blown on the QVD load, hopefully this clears things up, sorry I forgot about this on my first post.

https://support.qlik.com/articles/000002500

Regards,
Brett

[sangeeth8787]

Hi,

Does section Access should be first in Script, then how can we load qvd and use temp table in Section Access.If you don't mind, could you create a example qvw file.Many Thanks

Thank you