4 Replies Latest reply: Mar 29, 2016 4:39 AM by Simone Trabattoni RSS

    Section access with DataBase

    Simone Trabattoni

      Hi Community!

      I'm working on section access of an app on the Hub of Qlik Sense, of course not Desktop.

      Now I have a problem  with the section access because, if with the load inline it is easy to do, using DataBase field is quite problematic to me. Let's be more clear with an example, here my code:

       

      section access;

      LOAD

           'USER' as ACCESS

          ,'DIRECTORY\user' as USERID  

          ,Autonumber(field1&field2) as KEY

          ;

        SQL SELECT

           t1.field1

          ,field2

           FROM dbhost.Table1 t1

          LEFT JOIN dbhost.Table2 t2 ON t1.field1 = t2.field1

          ;

       

      A small comment: it is clearly an attempt, because I've not got some field -till now- in the db, so I decided to replace them with costants (ACCESS and USERID fields).

      The result I'm going to have is (the section access table is hidden to me, so I tried to create this table without the "section access" title):

       

       

      ACCESSUSERIDKEY
      USERDIRECTORY\user1
      USERDIRECTORY\user2
      USERDIRECTORY\user3
      .........
      USERDIRECTORY\usern

       

      In this example, there is going to be only one user with level user, that is going to see all the key-related data, and this is my goal till now. Last comment is that the "Autonumber" is compulsory to me.

       

      The result unfortunately is that I'm locked out from my app -of course when the section access is wrong-.

      It is quite strange because the translation of those data with a section access made with a load inline give me the result I want.

      So the question is: where is the problem? Section access does not work with db and/or Autonumber?

      It is a problem because I need it.

       

      Thanks a lot.

        • Re: Section access with DataBase
          Jonathan Pitchford

          Here is an example of section access from database tables.

           

          Try two database tables like this:

                

          REPORT_TBL
          REPORT_CODEREPORT_DESCSA1SA2SA3
          REPORT1QlikView Report 1FIELD_RESTRICT
          REPORT2QlikView Report 2FLD_RESTRICT
          REPORT3QlikView Report 3FLDS_RESTRICT

           

           

          USER_REPORT_TBL

          ACCESSNTNAMESA1SA2SA3REPORT_CODE
          ADMINUser1* REPORT1
          USERUser2AB REPORT1
          USERUser3AC REPORT1
          USERUser4AD REPORT1

           

           

          Section Access;

          LET vREPORT_CODE = UPPER('REPORT1');

           

          \\Database Connection to the two tables

           

           

          SECTION_ACCESS_FIELDNAME:

          LOAD `REPORT_CODE`,

              `REPORT_DESC`,

              SA1,

              SA2,

              SA3;

          SQL SELECT *

          FROM `REPORT_TBL`

          WHERE REPORT_CODE = '$(vREPORT_CODE)';

           

           

          LET vSA1 = UPPER(peek('SA1',0,'SECTION_ACCESS_FIELDNAME'));

          LET vSA2 = UPPER(peek('SA2',0,'SECTION_ACCESS_FIELDNAME'));

          LET vSA3 = UPPER(peek('SA3',0,'SECTION_ACCESS_FIELDNAME'));

           

           

          SECTION_ACCESS:

          LOAD

            UPPER(ACCESS) as ACCESS,

               UPPER(NTNAME) as NTNAME,

               SA1 as $(vSA1);

          SQL SELECT *

          FROM `USER_REPORT_TBL`

          WHERE REPORT_CODE = '$(vREPORT_CODE)';

           

          drop table SECTION_ACCESS_FIELDNAME;

           

          cheers jonathan

          • Re: Section access with DataBase
            Dalton Ruer

            Hopefully the video I've created helps you with your task: