Qlik Community

New to Qlik Sense

If you’re new to Qlik Sense, start with this Discussion Board and get up-to-speed quickly.

Announcements
QlikWorld 2022, LIVE in Denver CO., May 16-19, 2022. REGISTER NOW TO RECEIVE EARLY BIRD PRICING
cancel
Showing results for 
Search instead for 
Did you mean: 
jlinchec
Contributor II
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!

2 Replies
satishkurra
Specialist II
Specialist II

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
Contributor II
Contributor II
Author

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