Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements

Breathe easy -- you now have more time to plan your next steps with Qlik!
QlikView 11.2 Extended Support is now valid through December 31, 2020. Click here for more information.

Not applicable

Filter with two directions

Hi, all

I have a data model like this. (with more fields)

error loading image

The customers access to QV with Section Access using KEY.

This model filters well but DETAIL table and CLIENT table have millions of rows, so I want to put other filter from CLIENT table, using COMPANY.

I tried to do that only adding COMPANY in Section Access but it doesn't work properly.


/*************************** Section Access ****************************/
Section Access;

Access:

Load *
Inline
[USERID,ACCESS,PASSWORD,KEY,COMPANY
ADMIN,ADMIN,ADMIN,1,A
USER1,USER,PASSWORD,1,A
USER2,USER,PASSWORD,2,A
USER3,USER,PASSWORD,3,B
];


Is it possible to filter with KEY and COMPANY at the same time?

(And due to other reasons, I can't change this model.)

Thanks. S.I.

8 Replies
prieper
Honored Contributor II

Filter with two directions

Think that you need to separate the steps, in the first step you may just steer the access: ADMIN or USER, USERID, Password (and Group). This would only regulate, who is allowed to open the document, and whether he is USER or ADMIN. In a second table you then may regulate access - either for each user on USERID or on the group for each other field in the application. Chapter 30.8 of the manual may guide you a bit further.
Please remember that all entries should be done in capitals and resp. tickmarks in the document-properties should be set ("Initial Data Reduction ...")

Peter

Not applicable

Filter with two directions

Hi, Peter

Thanks fot your reply.

I did not explain enough. I had done the Section Access that you explain to filter by KEY and it works well.

What I want to do now is below:

A cuetomer entered using a KEY (Section Access). Only having a KEY, it is possible to filter well because a KEY depends on a COMPANY. (eg, KEY: 1A, 2A or 2B and COMPANY: A, B or C)

Due to a huge number of rows, now I want customers to have another type of filter (COMPANY) in addition to KEY. In fact, COMPANY's filter is not neccesary to work.

I tried this case with the code that I've post, but it does not work well.

Is it possible to obtain higher performance with additional filter (COMPANY)? and

Is it possible to filter data from two directions without changing the model like this sketch?

Thanks, S.I.

prieper
Honored Contributor II

Filter with two directions

I am not sure, whether I really get the point. You may add endless number of restrictions on other fields, the only restriction is that typically they are cumulative, i.e. if you restrict user A to see only Clients 1, 2, 3 and further restrict the user to see only company XYZ than he will only be able to see clients 1,2,3 if they simulteneously belong to compay XYZ.

Peter

Not applicable

Filter with two directions

Maybe you could add the company key into the KEY field ? In this way you would not have to split the section access fields ?

Or you create another table, let's call it GROUP_TABLE where there is 3 fields : KEY, COMPANY and RIGHTS_KEY which is concatenation of KEY and COMPANY. Then you add the Section access field RIGHTS_KEY ?

Rgds,

Sébastien

Not applicable

Filter with two directions

> Hi, Peter and Sébastien

Thank you for your reply!

The KEY field contains data that filter COMPANY data (eg. "Type1_CompanyX"), so only with a KEY works properly, filtering well also COMPANY data.

So I know it is not necessary to add COMPANY field in Section Access, but I want to try to filter not only by KEY but also COMPANY (I've explained above), if it would work better.

What I want to try makes sense?

Thanks, S.I.

Not applicable

Filter with two directions

we are facing a similar situation.

Even when you are able to filter data for a particular customer through the KEY field, the performance of the report in not good due to the fact that Detail and Client tables are large tables and joining by client field is taking so long. If the Client table would be previously filtered by COMPANY, this would reduce the size and therefore would increase performance (let me know if I didn't understand you right)

Unfortunately I haven't found a valid solution for this problem either, but any feedback will be welcome!!

Not applicable

Filter with two directions

any suggestions?

rob_greene
New Contributor III

Re: Filter with two directions

I can see it's been a while since the original post, but this might help someone.

I have had a problem recently where I created an optimised table so everything loaded nice and quick, but in section access you cannot load an optimised table. The only way I have found to get around this is to UN-OPTIMISE the load by doing a where clause.

Section Access ;

Load * from optimised_table.qvd (qvd)

where 1=1;

Section Application ;

Hope this helps

Regards

Rob

Community Browser