Hi Pradosh
Here is a portion of the section access which I have up to now been unable to implement. I have some client who wants to share their app with their suppliers. If a user from Supplier A logs on they should be able to clearly see their records but also be able to benchmark against their competition (Suppler B, Supplier C etc).without know who these are. The systems administrator shoule be able to see all records without being de-identified. I have tried to follow some direction on post https://community.qlik.com/docs/DOC-18235 but have not gotten the desired results. So as i was testing thats when i started experiencing the access issue when I try to access the app as another user who should see the other recored as de-identified. I am needing to de-identify the Group and the Company across about 19 groups. Find below the script. I could not locate how i could attache the whole app.
UserList:
load * inline [
USERID, Insurance_Group__c
advancenet\hilda.mortu,'Auction Nation'
advancenet\psd.reports1,Bryte
advancenet\edingtonz,Brolink
advancenet\nehemiahk,<ANY>
advancenet\Train1,Discovery
advancenet\train2,'Epic Underwriting'
advancenet\train3,Excelsure
advancenet\train4,Hallmark
advancenet\train4,Integrisure
advancenet\psd.train2,Integritas
advancenet\psd,IWYZE
advancenet\reports1,'Lion of Africa'
advancenet\train6,MiWay
advancenet\train7,MUA
advancenet\train8,'Old Mutual Insure'
advancenet\train9,Outsurance
advancenet\train11,Santam
advancenet\Train12,'Standard Bank'
advancenet\Train13,Telesure
advancenet\train14,'Tetelestai Risk'
advancenet\train15,Xenturion
advancenet\train16,YouI
];
//Load Invoice Line Details
[Invoice Line Detail]:
LOAD CreatedDate AS InvLineDateCreated,
Invoice__c AS InvoiceNo,
Year(CreatedDate) & ' - '& Month(CreatedDate) AS PeriodNameInv,
Year(CreatedDate) AS YearInv,
MOnth(CreatedDate) as MonthInv,
Line_Item_Amount__c,
Line_Item_Total__c,
Code__c,
RecordType_Name__c,
Insured_Value__c,
Insured_value_ext_vat__c,
Invoice_Paid__c,
InvoiceRecordTypeName__c,
Insurance_Group__c,
Insurance_Company__c,
Keys__c,
Starts__c,
Claim_Number__c,
Documentation_recieved_date__c
WHERE YEAR(Documentation_recieved_date__c) >=2017
AND CreatedDate>=2017;
SELECT * FROM Invoice_Line_Item__c;
// load users deidentified permissions
ACL_Users_Step1:
load distinct
USERID as USERID,
'USER' as ACCESS,
0 as _FlagIdentified,
Insurance_Group__c as _UserInsuranceGroup
// Insurance_Company__c as _UserInsuranceCompany
resident UserList
WHERE upper(Insurance_Group__c) <> '<ANY>' ;
// join ALL possible Insurance Groups
join (ACL_Users_Step1)
load distinct
Insurance_Group__c as _FactInsuranceGroup
//Insurance_Company__c as _FactInsuranceCompany
resident [Invoice Line Detail];
// de-identified Insurance Groups and Brokers
ACL_Users:
noconcatenate
load distinct
USERID,
ACCESS,
_FlagIdentified,
_FactInsuranceGroup
// _FactInsuranceCompany
resident ACL_Users_Step1
where _FactInsuranceGroup <> _UserInsuranceGroup;// and [_FactInsuranceCompany] <> [_UserInsuranceCompany]; // keep ALL Insurance Groups except for the user's Insurance Group
drop table ACL_Users_Step1 ;
// load permissions for access ANY
ACL_Users_ANY:
noconcatenate
load distinct
USERID as USERID,
'USER' as ACCESS,
1 as _FlagIdentified
resident UserList
where upper(Insurance_Group__c) = '<ANY>' ; //and upper(Insurance_Company__c) = '<ANY>';
// join ALL possible Insurance Groups
join (ACL_Users_ANY)
load distinct
Insurance_Group__c as _FactInsuranceGroup
//Insurance_Company__c as _FactInsuranceCompany
resident [Invoice Line Detail];
concatenate (ACL_Users)
load distinct
USERID,
ACCESS,
_FlagIdentified,
_FactInsuranceGroup
// _FactInsuranceCompany
resident ACL_Users_ANY;
drop table ACL_Users_ANY;
// load users identified permissions
concatenate (ACL_Users)
load distinct
USERID as USERID,
'USER' as ACCESS,
1 as _FlagIdentified,
Insurance_Group__c as _FactInsuranceGroup
//Insurance_Company__c as _FactInsuranceCompany
resident UserList;
drop table UserList;
//UNCOMMENT THIS CODE BLOCK STARTING HERE
section access;
//create section table for users with a reduction field called ACCESS_KEY
SA:
noconcatenate load distinct
upper(USERID) as USERID,
upper(ACCESS) as ACCESS,
upper(_FlagIdentified & '--' & _FactInsuranceGroup) as ACCESS_KEY
//upper(_FlagIdentified & '--' & _FactInsuranceCompany) as ACCESS_KEY_2
resident ACL_Users;
// add an admin user to the section access table with access to all Insurance Groups
SA:
load distinct
'advancenet\nehemiahk' as USERID,
'ADMIN' as ACCESS,
1 & '--' & upper(Insurance_Group__c) as ACCESS_KEY
// 1 & '--' & upper(Insurance_Company__c) as ACCESS_KEY_2
resident [Invoice Line Detail];
section application;
//ENDING HERE
// load fact data and set flag identified to 1 & rename fields with sensitive data
Fact:
load
upper(_FlagIdentified & '--' & Insurance_Group__c) as ACCESS_KEY, // upper case required as this field is used for section access
//upper(_FlagIdentified & '--' & Insurance_Company__c) as ACCESS_KEY_2,
*
;
load
1 as _FlagIdentified,
Insurance_Group__c as _InsuranceGroup,
Insurance_Company__c as _InsuranceCompany,
*
resident [Invoice Line Detail];
// load fact data again and set flag identified to 0 & rename fields with sensitive data
concatenate (Fact)
load
upper(_FlagIdentified & '--' & Insurance_Group__c) as ACCESS_KEY, // upper case required as this field is used for section access
//upper(_FlagIdentified & '--' & Insurance_Company__c) as ACCESS_KEY_2
*
;
load
0 as _FlagIdentified,
'De- Identified Insurance Group' as _InsuranceGroup,
'De- Identified Insurance Company' as _InsuranceCompany,
*
resident [Invoice Line Detail];
drop table [Invoice Line Detail];
drop table ACL_Users;
// drop sensitive fields
drop fields Insurance_Group__c,Insurance_Company__c;
// rename sensitive fields back
rename field _InsuranceGroup to Insurance_Group__c ;
rename field _InsuranceCompany to Insurance_Company__c;
