topic Re: Section access resident load '*' values in QlikView

Section access resident load '*' values

YoussefBelloum — Wed, 25 Nov 2020 16:16:04 GMT

Hi everybody,

Is there any restriction loading NTNAME or USERID fields having '*' values on the previous load in a Section Access table ?

Thank you

Re: Section access resident load '*' values

Anil_Babu_Samineni — Thu, 19 Jul 2018 14:56:39 GMT

Previous Load ?? As far i came across no..

Re: Section access resident load '*' values

PabloTrevisan — Thu, 19 Jul 2018 15:02:05 GMT

Let me see if I understand, you want to load the USERID field with a '*'? Usually the * is used for a USERID to have access to everything, and this is done like this:

Section Access;

LOAD * inline [

ACCESS, USERID, REDUCTION

ADMIN, DOMAIN\USER1, USER1

USER, DOMAIN\USER2, USER2

USER, INTERNAL\SA_SCHEDULER,,,*

];

Section Application;

Star is *;

REDUCT:

Load * InLine [

REDUCTION, CONTRY

USER1, *

USER2, USA

];

I may not have understood your questioning. =]

Re: Section access resident load '*' values

YoussefBelloum — Thu, 19 Jul 2018 15:08:34 GMT

Yes previous load..

example:

SECURITY_TMP1:

LOAD ACCESS,

NTNAME

FROM..

SECURITY_TMP2:

load ACCESS,

'*' as USERID,

NTNAME,

Resident SECURITY_TMP1:

as soon as i Re-call the USERID with a resident (after I put the '*' on it), section access don't work anymore (and If I don't add some Administrators giving them a USERID Value and an * on the NTNAME) I loose the document..

the only way is when I I call the table with a resident, is to Create again that field ('*' as USERID).. so don't make sense to insert '*' values before the last load.

Re: Section access resident load '*' values

YoussefBelloum — Thu, 19 Jul 2018 15:14:07 GMT

I work with both NTNAME and USERID fields because I'm using two server (on DEV and one PROD).

on the PROD I use NTNAME and Put '*' on the USERID

on the DEV I use USERID and put '*' on the NTNAME (to manually insert access and test the restrictions and verify that everything is working good on the section access)

Re: Section access resident load '*' values

Anil_Babu_Samineni — Thu, 19 Jul 2018 15:15:54 GMT

Did you understand how USERID calling here? and What is the diff between USERID and NTNAME

Re: Section access resident load '*' values

YoussefBelloum — Thu, 19 Jul 2018 15:23:21 GMT

Yes, our customers values are on the NTNAME and on the USERID we use the network administrators accounts, so we use both

Re: Section access resident load '*' values

marcus_sommer — Thu, 19 Jul 2018 15:30:00 GMT

Why not just using two different section access statements and branching them with something like:

if ComputerName() = 'Prod' then

SectionAccess1

else

SectionAccess2

end if

- Marcus

Re: Section access resident load '*' values

YoussefBelloum — Thu, 19 Jul 2018 15:34:49 GMT

That's exactly what I have now in front of me.. a variable using ComputerName().. it didn't work because I've tried:

If( not match(that_variable,'server1','server2')

put * on the NTNAME and use NTNAME as USERID (to be able to enter manually the creditentials)..

else

* as USERID, NTNAME

it didn't work because we have two PROD server, one dedicated to the publisher and one dedicated to the reload..

(but this is another problem..)

my question like you can read above, is that '*' values are not recognized anymore if load them using resident

Re: Section access resident load '*' values

YoussefBelloum — Thu, 19 Jul 2018 15:46:41 GMT

here is the security I've implemented and managing today:

Section access;

// ADMIN_ACCESS (NO RESTIRICTION)

SECURITY:

LOAD ACCESS,

NTNAME

FROM

// JOIN ALL THE BU, ALL THE G21 AND ALL THE G3 AND ALL THE SALES_MAN FOR ADMIN_ACCESS

left join

load distinct

BU,

'ALL_CUSTOMERS' as G21_SECURITY,

'ALL_CUSTOMERS' as G3_SECURITY,

'ALL_CUSTOMERS' as SALES_MAN_SECURITY

resident BusinessUnit;

// DIVISION_ACCESS (RESTRICTED BY BU)

concatenate (SECURITY)

load *,

'ALL_CUSTOMERS' as G21_SECURITY,

'ALL_CUSTOMERS' as G3_SECURITY,

'ALL_CUSTOMERS' as SALES_MAN_SECURITY

FROM

// G12_ACCESS (RESTRICTED BY BU, G21, G3,SALES_MAN)

TMP_KAM:

load 1 as Flag_anticoncatenate,

ACCESS,

upper(NTNAME) as NTNAME,

upper(BU) as BU,

upper(G21) as G21_SECURITY,

upper(G3) as G3_SECURITY,

upper(SALES_MAN) as SALES_MAN_SECURITY

FROM

// CONCATENATE G12_ACCESS TABLE ON THE SECURITY TABLE

concatenate (SECURITY)

load ACCESS,

NTNAME,

BU,

G21_SECURITY,

G3_SECURITY,

SALES_MAN_SECURITY

resident TMP_KAM;

// GIVE A USEDID (*) FOR ALL THE USERS AND CREATE THE SECURITY KEY

SECURITY_WINDOWS_TMP:

load ACCESS,

'*' as USERID,

NTNAME,

BU&'|'&G21_SECURITY&'|'&G3_SECURITY&'|'&SALES_MAN_SECURITY as KEY_SECURITY

resident SECURITY;

drop table SECURITY;

drop table TMP_KAM;

///////////////////////////////////////////// START SCRIPT CHECKING THE DEV AND PROD ENVIRONMENT TO SWITCH NTNAME AND USERID (ASKING FOR CREDITENTIALS ON LOCAL AND DEV) ///////////////////////////////////////

//IF ON LOCAL MACHINE OR DEV SERVER (ASK FOR CREDITENTIALS)

TRACE $(vComputerName);

IF NOT Match('$(vComputerName)','LOO9-S00QV1','LOO9-S00QV2') THEN

SECURITY_WINDOWS:

NoConcatenate

LOAD ACCESS,

'*' as NTNAME,

NTNAME as USERID,

KEY_SECURITY

Resident SECURITY_WINDOWS_TMP;

DROP Table SECURITY_WINDOWS_TMP;

// SERVER_ACCESS (BACKUP ACCESS AND NETWORK ADMINISTRATOR)

concatenate (SECURITY_WINDOWS)

load ACCESS,

USERID,

NTNAME,

KEY_SECURITY

FROM

//ELSE IF ON PRODUCTION SERVER MACHINE (APPLY NORMAL SECURITY)

ELSE

SECURITY_WINDOWS:

NoConcatenate

LOAD *

Resident SECURITY_WINDOWS_TMP;

DROP Table SECURITY_WINDOWS_TMP;

// SERVER_ACCESS (BACKUP ACCESS AND NETWORK ADMINISTRATOR)

concatenate (SECURITY_WINDOWS)

load ACCESS,

USERID,

NTNAME,

KEY_SECURITY

FROM

ENDIF

///////////////////////////////////////////// END SCRIPT CHECKING THE DEV AND PROD ENVIRONMENT TO SWITCH NTNAME AND USERID (ASKING FOR CREDITENTIALS ON LOCAL AND DEV) ///////////////////////////////////////

Section Application;

finally the test detecting the DEV and PROD is not working yet because it is not working with the ComputerName(), I will maybe try to detect the name of the harddrive on which the app is copied.

but the GREYED part is the part that present a problem for me, I removed the test, If I don't remove this part (and so loading a previously '*' values on USERID) the section access don't work anymore

Re: Section access resident load '*' values

marcus_sommer — Thu, 19 Jul 2018 15:56:52 GMT

Normally the approach should work even by using multiple server. Maybe there is just a small syntax/logic issue like using single-quotes for the variable: '$(that_variable)' or in the way the variable is created (did the value really change on the different servers) or how they is named (maybe there is just a typo). Beside this I think I wouldn't use a variable else just the function like above mentioned.

Another way to get the name of the server or maybe even the whole load-statement is to use a separate include-variable just placed within root-folder of your Qlik environment.

Regarding to your '*' issue - could it be that both loadings happens within the section access area? If yes try it by moving the first load above it and only the resident-load is inside the section access.

- Marcus

Re: Section access resident load '*' values

YoussefBelloum — Thu, 19 Jul 2018 16:02:00 GMT

I'll let you take a look at the below script and tell me what's your thoughts.

what is the trick concerning preparing the security table outside the section access and call the final table inside the section access ?

Re: Section access resident load '*' values

marcus_sommer — Fri, 20 Jul 2018 05:27:55 GMT

I don't really know how section access is technically implemented but there must be at least one essential part within the section access area - and if it's just some kind of indexing to the related fields and fieldvalues - and the final work might be applied after finishing the reload and building the datamodel.

This is a deduction from the fact that an optimized qvd-load within the section access doesn't work because there won't any processing applied on the data else they are just transferred within the RAM.

This could be easily bypassed with adding a where-clause like: where 1=1 to the load which then enforced a processing of the data. Your resident load contained also no further processing and might be therefore also run in an optimized mode (I'm not absolutely sure if a qvd- and a resident-load work in the same way but I could imagine it - only the storage will with hard-disc respectively RAM be different).

This meant such where-clause might be helpful in your case. But I assume that rather your creating of multiple different section access tables at the same time - at least with the noconcatenate load - caused your issue. Maybe they create some kind of synthetic key or circular loops or something else - probably it conflicts in any way.

Therefore my suggestion to build the section access tables outside from the section access area and loading there just the final table with such a dummy where-clause.

- Marcus

Re: Section access resident load '*' values

YoussefBelloum — Fri, 20 Jul 2018 08:34:15 GMT

Thank you so much marcus_sommer for these clarifications, didn't know about it.. I'll try to add a dummy where clause.

Yesterday my collegue here told me that he was not able to load '*' values from resident loads (in a section access table), but from txt files, from a table, from a qvd it is ok.. I'll also try to confirm this.

Re: Section access resident load '*' values

YoussefBelloum — Wed, 25 Jul 2018 09:10:02 GMT

Hi marcus_sommer

On the greyed part, using a dummy where clause (where 1=1) didn't help getting the below script work, but inserting again '*' values on the USERID field (on the greyed part) solved the issue..