QlikView App Dev

[#1]

Hi QlikExperts,

I have a question regarding restriction of data within fields, based on selections on another field. I have tried to put an example of the data below:

Actual Data: (possible values)

profile	department	customer	type
P0A	DeptA1	CustomerA1	Type1
P0A	DeptA1	CustomerA2	Type2
P0A	DeptA1	CustomerA3	Type1
P0A	DeptA1	CustomerB1	Type1
P0A	DeptA1	CustomerB2	Type2
P0A	DeptA1	CustomerB3	Type1
P0A	DeptB1	CustomerA1	Type1
P0A	DeptB1	CustomerA2	Type2
P0A	DeptB1	CustomerA3	Type1
P0A	DeptB1	CustomerB1	Type1
P0A	DeptB1	CustomerB2	Type2
P0A	DeptB1	CustomerB3	Type1

Security Part: (what should the user logged in with specific profile see)

profile	department	customer	type
P0A	DeptA1	CustomerA1	Type1
P0A	DeptA1	CustomerA2	Type2
P0A	DeptA1	CustomerA3	Type1
P0A	DeptB1	CustomerB1	Type1
P0A	DeptB1	CustomerB2	Type2
P0A	DeptB1	CustomerB3	Type1

As seen in the example above, Profile P0A should see only CustomerA1, CustomerA2 and CustomerA3 and the data in field type, when he selects DeptA1.

But when he selects DeptB1, he should see the relevant data as seen in the above table.

However, in section access I have mentioned P0A sees DeptA1 and DeptB1 only.

Please can someone help. Hope I have best described the problem case.

Thanks in advance,

Robinson

[#2]

Do any users need to see the combination of DeptA1 and CustomerB1 together?

If not, I would simply remove those records from the data in the script.

[#3]

Hi Michael,

Thanks for the quick reply.

Let me put it this way:

profile	department	customer	type
P0A	DeptA1	CustomerA1	Type1
P0A	DeptA1	CustomerA2	Type1
P0A	DeptA1	CustomerA3	Type1
P0A	DeptB1	ALL	ALL
P0A	DeptB1	ALL	ALL
P0A	DeptB1	ALL	ALL

So if P0A selects Dept1A he should see restricted data only, but if he selects DeptB1, he should see all data.

In other words, this is restriction of data (section access), based on user selections.

Thanks,

Robinson

[#4]

You will probably need to create a mapping table that contains all the combinations that are allowable.

[#5]

Hi Michael,

You mean to create an excel table with allowable combinations for different profiles?

Create a key out of the key fields in that excel an link it to the fact table in the data model. Then make users select the fields from the excel file?

But will that be a feasable way to do? Lets say the number of profiles increase, so does the probable combinations (eg. a few more fields will be added for data restrictions).

Will it not be a pain to maintain the excel file?

Currently I have 11 profiles, out of which 2 of them have the above restrictions. If I define a mapping table for the 2 profiles, I will have to add the other profiles with all combinations also, as I would be using the fields from the excel?

I had tried above approach, but was giving some issues once i add in the profiles in excel. Will have to give it another try.

Thanks again,

Robinson

[#6]

I don't think you should need all possible combinations for profiles that have no restriction.  Only those that do have a restriction.  You may be able to use joins in the script to build a table of all possible combinations if that helps.

Obviously a complicated security model like this is always going to cause maintainance issues.

[#7]

Hi Michael,

The challenge is all the profiles have some sort of restriction on the fields.. There is only 1 profile, which does not have restriction, behaves probable like a super user...

Thanks,

Robinson

[IAMDV]

Hi Robinson,

You need multi level/ Hieararchy based on Section Access Field Reduction. You need to be very careful while building something like this and please do thorough testing. Also, it gets more complex if you want to use "Strict Exclusion" option. Here is an idea to get started and this apprach is NOT using "Strict Exclusion" and with this approach you need to make sure that Section Access Reduction Field Value MUST EXIST witin your data model. If the value doesn't exist then Users will get full access. So you need to build a logic to remove the Users who don't have matching field value in Data Model compared to Section Access Reduction Field.  I'm attaching the sample application along with the script...

LOAD

UPPER(Profile) AS PROFILE,
UPPER(Department) AS DEPARTMENT,
UPPER(Customer) AS CUSTOMER,
UPPER(Type) AS TYPE,
UPPER(Profile) AS KEY_01,
UPPER(Profile) &'_' & UPPER(Department) AS KEY_02,
UPPER(Profile) &'_' & UPPER(Department) &'_' & UPPER(Customer) AS KEY_03,
UPPER(Profile) &'_' & UPPER(Department) &'_' & UPPER(Customer) &'_' & UPPER(Type) AS KEY_04
FROM
[..\Security based on Selection of data.xlsx]
(ooxml, embeddedlabels, tableis Sheet1);

SECTION ACCESS;
LOAD * INLINE [
ACCESS, USERID, PASSWORD, KEY_01, KEY_02, KEY_03, KEY_04
ADMIN, ADMIN, ADMIN,
USER, P0A, P0A,,,P0A_DEPTA1_CUSTOMERA1,
USER, P0A, P0A,,,P0A_DEPTA1_CUSTOMERA2,
USER, P0A, P0A,,,P0A_DEPTA1_CUSTOMERA3,
USER, P0A, P0A,,,P0A_DEPTB1_CUSTOMERA1,
USER, P0A, P0A,,,P0A_DEPTB1_CUSTOMERA2,
USER, P0A, P0A,,,P0A_DEPTB1_CUSTOMERA3,
USER, P0A, P0A,,,P0A_DEPTB1_CUSTOMERB1,
USER, P0A, P0A,,,P0A_DEPTB1_CUSTOMERB2,
USER, P0A, P0A,,,P0A_DEPTB1_CUSTOMERB3,
];

SECTION Application;

I hope this makes sense!

Cheers,

DV

www.QlikShare.com

[#9]

Hi Deepak,

Thanks for the reply.

From your example qvw, the ones marked in green should be the desired output (restricted values in CUSTOMER AND TYPE), for the respective selection (DEPARTMENT).

To elaborate more, the desired output should be something like this:

profile	department	customer	type
P0A	DeptA1	CustomerA1	Type1
P0A	DeptA1	CustomerA2	Type1
P0A	DeptA1	CustomerA3	Type1
P0A	DeptB1	CustomerA1	Type1
P0A	DeptB1	CustomerA1	Type2
P0A	DeptB1	CustomerA2	Type1
P0A	DeptB1	CustomerA2	Type2
P0A	DeptB1	CustomerA3	Type1
P0A	DeptB1	CustomerA3	Type2
P0A	DeptB1	CustomerB1	Type1
P0A	DeptB1	CustomerB1	Type2
P0A	DeptB1	CustomerB2	Type1
P0A	DeptB1	CustomerB2	Type2
P0A	DeptB1	CustomerB3	Type1
P0A	DeptB1	CustomerB3	Type2

And to add more to the complexity, there are also measures that should be hidden based on the above restricted selections.

Thanks,

Robinson

[#10]

Hi Deepak,

To be more precise about the securities, I have tried to simulate the requirement (maybe it will be more clear than my example posted above), and have attached an excel file for the same.

In reference with the excel file

Selection Filters are the fields available for selection. ALL means all possible values should be available.

Input Indicators are nothing but measures. Yes means the measure should evaluate to zero (value should not be displayed) No means the measure should be visible.

A Selection means values for these will be populated from the database - I can manage this one.

Sorry for the complexity.

Thanks,

Robinson