Ignoring NULL in specific table in Section Access

[user467341]

Hi Qlik community,

I have the following subset of my data model below

I have successfully implemented the section access control by USER_ACCESS level

Section Access;
[AUTHORIZE]:
LOAD ACCESS, USERID, GROUP, SUBFIELD(USER_ACCESS, ';') AS USER_ACCESS;
LOAD * inline [
ACCESS, USERID, GROUP, USER_ACCESS
USER, TEAM\1234567, *, SALES;SUPPORT
USER, TEAM\1236789, *, CLOTHING
ADMIN, LEADER\6777777, LEAD ADMIN, *
ADMIN, INTERNAL\SA_SCHEDULER, LEAD ADMIN, *
];

Section Application;
[ReductionTable]:
LOAD
    USER_ACCESS,
    DEPARTMENT_ID
FROM [lib://xxxx.xlsx]
(ooxml, embedded labels, table is Sheet1);

The ClockInOut table successfully data reduced according to the section access implemented.

However, this propagate to the Order table. Originally, Order table will have 200 lines. Due to section access implementation, an ADMIN with full access will still see this table reduced to 30 lines. If I leave the USER_ACCESS for ADMIN blank, the full 200 lines will appear in Order (ADMIN access all data, including NULL).

This means that the NULL is causing this.

I need the Order table to show full 200 lines, regardless of the USER_ACCESS. The Order table is linked to more tables in the actual data model.

Is there a way to get full data, inclusive NULL, in Order while using section access? I am open to a workaround, bridge table, composite keys, generic keys though not too sure if all these are possible.

[marcus_sommer]

I think you need to replace NULL with a real value, like:

coalesce(Field, '<NULL>') or maybe if(len(trim(Field)), Field, '#NV')

which you could now list within the section access. Depending on visibility and accessibility of this field within the UI you may consider to double the field to have it with the origin and the adjusted content.

- Marcus

[user467341]

Hi Marcus,

Thanks for the reply.

I noticed that when I create a table using Order_No and Emp_ID as the two fields only, there are NULLs. The 30 lines appeared in section access are data rows that contains Order_No and Emp_ID. If section access is remove, out of the 200 lines, 170 lines are either missing the Order_No OR Emp_ID.

I tried to replace NULL before posting here but failed (maybe my IF statement was incorrect). I will try your method once I get home and post back. By the way, could you kindly explain what you meant by double the field? I don't quite follow there.

Thanks again.

[marcus_sommer]

Doubling the field means to load it more than once else several times - mostly twice but sometimes even more. The reason is that if you manipulate a field (replacing NULL, filling gaps, cleaning data, adding extra values, ...) you couldn't see the origin values anymore which may further important, for example to get a rate of the data-quality.

- Marcus