Section access with two reductions

[migueldfr_23]

Hello everyone

Hope everyone find well

I am facing with an issue regarding the section access topic. 

1. I find all the M-cold name in the active directory 

// Cargar la jerarquía de managers desde la fuente principal
LOAD 
    UPPER(manager_email) AS m_email,
    worker_id;

[hcm_hierarchy]:
SELECT *
FROM Aws"hr"."hierarchy";

// Conectar al origen LDAP
LIB CONNECT TO 'LDAP';

// Cargar datos del Active Directory directamente sin usar bucles ni variables
ActiveDirectory: 
LOAD 
    UPPER(mail) AS m_email,
    'M-COLD\' & UPPER(sAMAccountName) AS USR;
SQL SELECT
    mail,
    sAMAccountName
FROM 'LDAP/INTERNAL';

// Unión izquierda entre la jerarquía de managers y la tabla de Active Directory
LEFT JOIN (hcm_hierarchy)
LOAD
    UPPER(m_email) AS m_email,
    UPPER(USR) AS USERID
RESIDENT ActiveDirectory;

// Crear tabla temporal con los datos combinados
HIERARCHY_AD:
NoConcatenate LOAD
    m_email,
    worker_id,
    USERID
RESIDENT hcm_hierarchy;

// Eliminar tablas intermedias
DROP TABLE hcm_hierarchy;
DROP TABLE ActiveDirectory;

2. Secondly, I am building the section access

USERS:
LOAD * Inline [
ACCESS,USERID,REDUCTION
ADMIN,INTERNAL\SA_SCHEDULER,*
ADMIN,M-COLD\QSSERVICE,*
];


// Load the access list from the Excel file (specific to HR)
ACCESS_LIST:
LOAD
    UPPER(ACCESS) as ACCESS,
    UPPER(NTNAME) AS USERID
FROM [lib://FILES.01.SOURCE/Visibility/Visibility_HR_Workforce.xlsx]
(ooxml, embedded labels);

// Load hierarchy information (from Active Directory or another source)
HIERARCHY_AD1:
LOAD
    UPPER(USERID) as USERID,
    worker_id as REDUCTION
RESIDENT HIERARCHY_AD; // Ajusta según tu fuente de datos

// Join the access list with the hierarchy data to associate users with their REDUCTION
INNER JOIN (ACCESS_LIST)
LOAD
    USERID,
    REDUCTION
RESIDENT HIERARCHY_AD1;

// Limpia la tabla temporal de jerarquías
DROP TABLE HIERARCHY_AD1;

// Combinar la lista de usuarios con la lógica de ADMIN
CONCATENATE (USERS)
LOAD
    ACCESS,
    USERID,
    IF(ACCESS='ADMIN', '*', REDUCTION) as REDUCTION
RESIDENT ACCESS_LIST;

// Limpia la tabla de lista de acceso
//DROP TABLE ACCESS_LIST;

// Section Access: Carga la tabla final en la sección de acceso
SECTION ACCESS;
LOAD
    ACCESS,
    USERID,
    REDUCTION
RESIDENT USERS;

// Limpia la tabla temporal de usuarios
DROP TABLE USERS;

But I have this structure on the file, so I have to adjust this script with this information.
Once I set the General Manager, it has to see only the people behind, ignoring the user profile, onces Role does not has anything, pay attention on the user profile.

ACCESS	NTNAME	USERPROFILE	ROLE
ADMIN	USER1	*
USER	USER2	EE01
ADMIN	USER3	*	GENERAL MANAGER
ADMIN	USER4	*

Thank you in advance

[Ben_P]

Can you combine USERPROFILE & ROLE into a single field, and use that for the access restriction? 

[migueldfr_23]

Thanks for answering.
I change the second logic to make it easier and redeable.

Even though, it does not work, I guess I am approaching to the goald.

USERS:
LOAD * Inline [
ACCESS,USERID,REDUCTION, ROLE
ADMIN,INTERNAL\SA_SCHEDULER,*,
ADMIN,M-COLD\QSSERVICE,*,
ADMIN,M-COLD\C-ALBERTM,*,
ADMIN,M-COLD\C-MIOTTOA,*,
ADMIN,M-COLD\C-ASTOLFA,*,
];

// Load Access List from Excel
ACCESS_LIST:
LOAD
    UPPER(ACCESS) AS ACCESS,
    UPPER(NTNAME) AS USERID,
    UPPER(USERPROFILE) AS REDUCTION,
    UPPER(ROLE) AS ROLE
FROM [Visibility_HR_Workforce.xlsx]
(ooxml, embedded labels, table is Sheet1);


Left Join (USERS)
LOAD
    USERID,
    worker_id as REDUCTION_by_ID
Resident HIERARCHY_AD;

Drop Table HIERARCHY_AD;

SECTION ACCESS;
LOAD
    ACCESS,
    USERID,
    IF(ROLE = 'GM', REDUCTION_by_ID, REDUCTION) AS REDUCTION
Resident USERS;

As you said before, I get the result of that table with only three columns.

Does it make sense ? 

Thank you 

[Ben_P]

This might be of use: Data Reduction Using Multiple Fields - Qlik Community - 1474917

[migueldfr_23]

Thttps://community.qlik.com/t5/Design/Authorization-using-a-Hierarchy/ba-p/1476319#comments

This will be the one, trying to understand and replicate in my case ...

[migueldfr_23]

Hello,

Would you know how to set "view as" as is in Power BI? In order to check how would see that specific user

Thank you

[Ben_P]

The way I do this is to just remove the 'section access' line in the script, so that you load the access permissions table as a normal table. Then you can just filter for a specific user in this table to see how they'd see the app.