Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

jlinchec
New Contributor II

Trouble with Section Access from SQL Server

I'm self teaching myself a bit on section access.

I have it working when I bring in the data either inline or from an Excel spreadsheet.

However, if I take that Excel spreadsheet and load it into SQL Server, and try to use the SQL Server table as the basis for section access, all my users get locked out.

The data is simple test data and the same between the two sources:

Excel:

excel.jpg

SQL Server:

SQLSERVER.jpg

Here is the script I use to load the Excel file, which works great:

section Access;
LOAD
    "ACCESS",
    "USERID",
    TEACHER_ID,
    OMIT
FROM [lib://Qlik Demo Data (ccg-pc0hc77k_qlik)/Modeling\Names Very Different\Security.xlsx]
(ooxml, embedded labels, table is Security);

section Application;

Here is the script I am using to load the SQL Server data.  Again this locks out all of my users:

SECTION ACCESS;
LIB CONNECT TO 'Microsoft_SQL_Server_localhost (ccg-pc0hc77k_qlik)';

[SQL_Security]:
LOAD "ACCESS",
"USERID",
TEACHER_ID,
OMIT;

SELECT [ACCESS],
[USERID],
[TEACHER_ID],
[OMIT]
FROM Education.dbo.Security;

SECTION Application;

TEACHER_ID is the field I am associating to the application model.  I've tried different combinations of using quotes and brackets with the field names, in case there are reserved words.

When I remove the section access flag and just look at how the SQL Server data is being loaded into Qlik, everything looks fine, as you can see it does the association to the application data which adds the additional row and column:

join.jpg

So it doesn't look like a data type issue or anything like that...

If anyone sees what I am missing here I'd appreciate a note!

Tags (1)
2 Replies
satishkurra
Valued Contributor II

Re: Trouble with Section Access from SQL Server

This is the default behavior of SQL SERVER i believe.

After loading in SQL SERVER, try below statement

UPDATE YourTable SET Column = '' WHERE Column = NULL


Thanks

jlinchec
New Contributor II

Re: Trouble with Section Access from SQL Server

I don't believe the NULLs are the issue, because even users who do not have NULL for TEACHER_ID cannot log in.

Also, when I updated the tables and set them to '', it ended up displaying 0 instead of NULL.

null.jpg

Community Browser