Section access with excel - Qlik ignores some of the rights

[midnight1]

Hi everyone 🙂

I have an app with section access from excel.

We categorise our suppliers into types, based on which we give access to app to users so that they will see data only with particular type of supplier.

This is excel:

As you can see, there are account that can see only one type of supplier(ex. account 1 only sees type 01) and others can see two types (ex. account 2 should be able to see type 02 and 11).
This is code :

 Section Access;
 Authorization:
 LOAD
     "ACCESS",
     "USERID",
     SUPPLIER_TYPE
 FROM [lib://AccessFiles/suppliers_types.xlsx]
 (ooxml, embedded labels, table is SectionAccess);

Section Application;

Table1:
LOAD  
	ArtId,
From ABC;

Left Join
LOAD
    ArtId,
    SupplierId,
FROM DEF;

Left Join
LOAD
    SupplierId,
    SupplierName,
    SUPPLIER_TYPE
FROM FGH;

Table2:
LOAD
    ArtId,
FROM sometable;

Left Join
LOAD
    ArtId,
    SupplierId,
    ProductCode,
FROM productCodes;

THE PROBLEM:  not every account see ProductCode 😞 
to be precise: accounts with SUPPLIER_TYPE 01,02,03,03,05 and 06 don't see any productCode, but THEY DO SEE productCode 11, 14 15 or 16

so:

but account2 (which should see 02 and 11):

you would think that account 2 see product code for supplier_type 02, but NO! 😞 it sees it for supplier_type 11.
If you choose supplier_type 02 in the filter, everything will be grayed out 😞

I tried changing the code in the way that this part:
Left Join
       LOAD
                ArtId,
                SupplierId,
                ProductCode,
FROM productCodes;

will be added to table 1 and not table 2

and then everything works!

BUT i cannot do that since it messes up the rest of application.
And there should something else what i can do since right now the supplier_type 11 or 14 or 15 or 16 works well.
There is problem with 01, 02, 03, 04, 05 and 06.

It is 01 because in the database in the source table it has zero at the beggining, so i had to change cell in excel to be text so it could hold 01 and not change it to 1.

How can I make account to see product codes for supplier type 01, 02, 03, 04, 05 and 06.?

[p_verkooijen]

@midnight1  Guess the SA part of the script converts the 01, 02, 03, 04, 05 and 06 to 1,2,3,4,5,6

You could test it when you just load it in an app to see the results (not within Section Access;)

You can force the values to 0x with NUM(field, '00')

NUM(SUPPLIER_TYPE, '00') AS SUPPLIER_TYPE

[midnight1]

Hi 🙂 thank you for this idea, I tried writing section access like this:

Section Access;
Authorization:
LOAD * INLINE [
ACCESS,       USERID,                                         SUPPLIER_TYPE
ADMIN,          DOMAIN\ADMINISTRATOR,
ADMIN,          DOMAIN\ACCOUNT0,
USER,            DOMAIN\ACCOUNT1,                         01
USER,            DOMAIN\ACCOUNT2,                         02
USER,            DOMAIN\ACCOUNT2,                         11
USER,            DOMAIN\ACCOUNT3,                         03
USER,            DOMAIN\ACCOUNT4,                         04
USER,            DOMAIN\ACCOUNT4,                         14
USER,            DOMAIN\ACCOUNT5,                         05
USER,            DOMAIN\ACCOUNT5,                         15
USER,            DOMAIN\ACCOUNT6,                         06
USER,            DOMAIN\ACCOUNT6,                         16
ADMIN,          INTERNAL\SA_SCHEDULER,
];

Section Application;

But it does not change anything 😞 from my developer account i see everything, but accounts still see for example only 11 and not 02.

I tried this (i'm not sure if this is what you were thinking about):
Section Access;
Authorization:
LOAD
"ACCESS",
"USERID",
NUM(SUPPLIER_TYPE, '00') as SUPPLIER_TYPE
 FROM [lib://AccessFiles/suppliers_types.xlsx]
(ooxml, embedded labels, table is SectionAccess);

but still account1 doesn't see ProductCode 😞

[marcus_sommer]

The formatting only per num() may not be suitable especially not if the linked field contained strings and not numbers. In this case you may try the load per:

Text(SUPPLIER_TYPE)

or per:

TEXT(NUM(SUPPLIER_TYPE, '00')) 

[Kushal_Chawda]

@midnight1  I don't understand the use of Table2 here. Anyway table1 & table2 creating synthetic key on ArtID & SupplierID. Instead you can join it to table1 which internally any way full join. See if you could change this part where you can use join instead keeping Table2 separate.

Table1:
LOAD  
	ArtId,
From ABC;

Left Join
LOAD
    ArtId,
    SupplierId,
FROM DEF;

Left Join
LOAD
    SupplierId,
    SupplierName,
    SUPPLIER_TYPE
FROM FGH;

join
LOAD
    ArtId,
FROM sometable;

Left Join
LOAD
    ArtId,
    SupplierId,
    ProductCode,
FROM productCodes;

[Qrishna]

We never know whats going on with the data until we see the output data after the joins even after doing the proper joins in the script.Pls Attach some sample data.