Qlik Community

QlikView Security & Governance

Discussion Board for collaboration on QlikView Security and Governance.

Not applicable

Section Access from .xls

Re. Qlik Sense

Hi,
I have a strange situation where access details loaded from an Excel spreadsheet don't work (i.e. "Access denied") whereas the same
details loaded INLINE do!

I've formatted the spreadsheet as text and tried trim() but it makes no difference.

//
Section Access;
// Load user list from secure .xls
Authorisation:
/*
set vAllStates = 'ACT,NSW,VIC,TAS,SA,WA,NT,QLD';
set vSthStates = 'VIC,TAS,SA,WA';

load ACCESS ,USERID ,subfield(STATECODE, ',') as STATECODE
inline
[ACCESS, USERID, STATECODE
'USER'  ,'DOMAIN\greg_thomas'  ,"$(vAllStates)"
  'USER'  ,'DOMAIN\john_jones'  ,"$(vSthStates)"
'ADMIN' ,'INTERNAL\sa_scheduler'  ,"$(vAllStates)"
    ];
*/

load trim(ACCESS)
,trim(USERID)
,trim(STATECODE)
;
load "Access"   as ACCESS
   ,[UserID]   as USERID
   ,subfield([State Code], ',') as STATECODE
FROM [lib://QSContent/App Access by user.xlsx]
(ooxml, embedded labels, table is Sheet1)
WHERE [Application] = 'PMO';


//
Section Application;

When I compare the Authorisation table in Data Manager there doesn't appear to be any difference.

Does anyone have any ideas please?

1 Solution

Accepted Solutions
Not applicable

Re: Section Access from .xls

Seems to have been related to the WHERE clause, you need to use UPPER().

When I comment out the Section Access the tables appear correct, but when Section Access is active it doesn't work.

QS automatically converts fields to upper-case even if they're not being used in the table.

3 Replies
sasikanth
Valued Contributor III

Re: Section Access from .xls

HI,

Use Capital letters for ACCESS and USERID in Excel it self , dont rename here

I am not sure but try once

Not applicable

Re: Section Access from .xls

Greg,

You can reference my question on section access posted on yesterday.

Cannot make section access work

My problem solved based on Maxgro suggestion on change USER to another name FIELD.

Hope it help.

Thanks.

Not applicable

Re: Section Access from .xls

Seems to have been related to the WHERE clause, you need to use UPPER().

When I comment out the Section Access the tables appear correct, but when Section Access is active it doesn't work.

QS automatically converts fields to upper-case even if they're not being used in the table.

Community Browser