Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

simotrab
Contributor II

Section access with DataBase

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.

1 Solution

Accepted Solutions
Employee
Employee

Re: Section access with DataBase

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

4 Replies
jonopitchford
New Contributor III

Re: Section access with DataBase

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

simotrab
Contributor II

Re: Section access with DataBase

Hi Jonathan,

first of all thanks for your answer.

I'm sorry that I've not pointed out explicitly, but I cannot to have some db table like the ones you're suggesting -neither create them-: this is why I've done the "trick" of the constant fields.

Employee
Employee

Re: Section access with DataBase

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

simotrab
Contributor II

Re: Section access with DataBase

Hi Dalton,

thanks for your video. I've resolved the problem using Upper() also on the USERID field: however your video is clear and helpful.

Thanks.