Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Not applicable

Section application and part of data

Hi,

I'm trying to give access to salesperson to the QV file and use same short codes that have been used for Salesman. Each salesman should only see their own data.

When I tried following script, then I got two options. Admin can see everything and all other users see all data, but cannot select Salesman.

Section Access;

LOAD * INLINE [

    ACCESS, USERID, PASSWORD, OMIT

    ADMIN, ADMIN, ADMIN,

    USER, LUCY, U1, SALESMAN

    USER, KIM, U2, SALESMAN

];

Section Application;

I tried different options for Section application, but none worked. How can I set up the script that when Lucy opens file then she has Salesman Lucy selected and cannot change it? As I have many people that will start to use the data, is there any variable option that can be created as Salesman field also has same code?

Thank you in advance!

Mariliis

Tags (1)
1 Solution

Accepted Solutions
pat_agen
Valued Contributor

Section application and part of data

hi,

in the example I have posted you asscociate each USER with a SALESMAN code inside section access. *

The SALESMAN code is then repeated inside the section application. This will then restrict the user to seeing the records having their respective SALESMAN values.

I am not sure that I follwo what you mean when talking of saving time and a variable option but what is sure is that you are not obliged to use a load * inline command in section access. This can be a load from a relevant data source.

So for example in your case you could do the following:

salesPersonSecurity:

load             SalesManCode,

                   upper(SalesManCode) as SALESSECURITY

from sales data source

group by SalesManCode;

Section Access;

star is *;

LOAD * INLINE [

    ACCESS, USERID, PASSWORD, SALESMAN

    ADMIN, ADMIN, ADMIN,  *

];

load    

     'USER'                    as ACCESS,

     SALESSECURITY   as USERID,

     SALESSECURITY   as SALESSECURITY,

     insertsomecodeheretodefineapassword                              as PASSWORD

];

Section Application;

sales:

load             *

from sales data source;

The above will link the table salesPersonSecurity to your sales table. The salesPersonSecurity table is where the security reduction/restriction will take place. Users will now only see data where their userid matches the salesman code in the sales table.

notes:

Matt quite rightly tells you to do backups. By applying section access you can potentialy lock yourself out of your document - a frustrating moment for any qv developer.

The load inline record for user ADMIN is necessary to avoid this particluar scenario.

the above code saves you having to write out your 50 sales people into section access but will require that ypou find someway of coding a password. Your security wont really be very secure if everyone knows that the userid is the same as the sales person code and how the passwords are generated.

If you have an active directory in place in your company than NT authentification is a neat way of getting around this - but in the long run it woudl be good to set up a security table somewhere to manage who sees what.

hope this is clear.

5 Replies
pat_agen
Valued Contributor

Section application and part of data

hi,

I am not sure if I have understood correctly so correct me if I am wrong.

You have sales data which includes a field called SALESMAN. This field contaisn values such as 'LUCY', 'KIM' etc. who are Sales people and also users of your qvw. You want each user to be able to open the document nad then only be able to see their data. Ie Lucy can iopen the document but only see records where 'LUCY' is teh value in the SALESMAN field.

Is this right?

if so code it as follows:

Section Access;

star is *;

LOAD * INLINE [

    ACCESS, USERID, PASSWORD, SALESMAN

    ADMIN, ADMIN, ADMIN,  *

    USER, LUCY, U1, LUCY

    USER, KIM, U2, KIM

];

Section Application;

sales:

load *,

      this load must include a field called SALESMAN with the appropriate upper case values such as 'LUCY', 'KIM' etc.

hope this helps.

matt_crowther
Valued Contributor

Section application and part of data

From your post it looks asthough you're missing the 'Section Application' load that associates your User to a particular value in a field to make the data reduction. In this case 'Lucy' as both User and Salesman.

The below is taken from the Help File (under 'Security'), as you can see the 'DEPT' field is in both sections and it is this that drives the reduction.

load * inline

[UserID,DEPT,Access

John,FINANCE,Admin

ANN,IS,Admin

Peter,SALES,User

Ben,TECH,User ] ;

section application;
star is *;

load * inline
[DEPT,CostCenter
FINANCE,*
IS,5020
SALES,5030
TECH,5040 ] ;

Also ensure that under Settings > Document Properties > General that you check 'Initial Data Reduction based on Section Access'.

If you need any more guidance check the Help file as it's a good place to start, I'd also recomend taking plenty of backups as you progress as one mistake can lead to you being locked out of your document with no way of getting back in.

Hope that helps,

Matt - Visual Analytics Ltd

Highlighted
Not applicable

Section application and part of data

Thank you both!

I did check QV Reference and on page 518-519 it got too complicated to understand Section application. Help explained it in a better way.

Now I have people who can open and see data they should and I can enter this for 50 users.

But to save time, question remains about any variable option?

For example I have 50 Sales persons who have same user code as the value in my file on field Salesman. All of them should only see data associated to themselves.

Can I perhaps show department as Sales to 50 users and then in section application part with one row that only to field that is matching with UserID?

pat_agen
Valued Contributor

Section application and part of data

hi,

in the example I have posted you asscociate each USER with a SALESMAN code inside section access. *

The SALESMAN code is then repeated inside the section application. This will then restrict the user to seeing the records having their respective SALESMAN values.

I am not sure that I follwo what you mean when talking of saving time and a variable option but what is sure is that you are not obliged to use a load * inline command in section access. This can be a load from a relevant data source.

So for example in your case you could do the following:

salesPersonSecurity:

load             SalesManCode,

                   upper(SalesManCode) as SALESSECURITY

from sales data source

group by SalesManCode;

Section Access;

star is *;

LOAD * INLINE [

    ACCESS, USERID, PASSWORD, SALESMAN

    ADMIN, ADMIN, ADMIN,  *

];

load    

     'USER'                    as ACCESS,

     SALESSECURITY   as USERID,

     SALESSECURITY   as SALESSECURITY,

     insertsomecodeheretodefineapassword                              as PASSWORD

];

Section Application;

sales:

load             *

from sales data source;

The above will link the table salesPersonSecurity to your sales table. The salesPersonSecurity table is where the security reduction/restriction will take place. Users will now only see data where their userid matches the salesman code in the sales table.

notes:

Matt quite rightly tells you to do backups. By applying section access you can potentialy lock yourself out of your document - a frustrating moment for any qv developer.

The load inline record for user ADMIN is necessary to avoid this particluar scenario.

the above code saves you having to write out your 50 sales people into section access but will require that ypou find someway of coding a password. Your security wont really be very secure if everyone knows that the userid is the same as the sales person code and how the passwords are generated.

If you have an active directory in place in your company than NT authentification is a neat way of getting around this - but in the long run it woudl be good to set up a security table somewhere to manage who sees what.

hope this is clear.

Not applicable

Section application and part of data

Thank you for your answer!

I know the options now and will consider NT authentication instead generating passwords as you suggested.

Community Browser