Section Access from .xls

[#1]

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?

[sasikanth]

HI,

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

I am not sure but try once

[#3]

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.

[#4 · Accepted solution]

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.