2 Replies Latest reply: May 20, 2017 11:15 PM by Jonathan Lincheck RSS

    Trouble with Section Access from SQL Server

    Jonathan Lincheck

      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!