topic Issue with Section Access - resident load and ntname in QlikView

Issue with Section Access - resident load and ntname

Wed, 11 Jul 2012 11:50:58 GMT

Hi,

Looking at implementing Section access via a resident load - my code below was working fine until I added the NTNAME field; however when I open the script the document is not recognising my account.

I have attached the table that I stored into the attached text file.

SECTION Access;

LOAD

USERID,

PASSWORD,

ACCESS,

PRODUCTKEY,

NTNAME,

IF(ACCESS='USER','PRODUCTKEY') AS OMIT Resident SecurityNew;

Thanks in advance - Chris

Re: Issue with Section Access - resident load and ntname

Wed, 11 Jul 2012 12:13:59 GMT

Hi Chris,

I tried it and worked fine. First using "ADMIN" user with USERID. Then, I added myself to NTNAME as you, reloaded and also worked.

Attached you can find the tiny app I used that worked for me (Section Access code commented just in case you couldn't open it for the first time).

Re: Issue with Section Access - resident load and ntname

Wed, 11 Jul 2012 13:01:53 GMT

Hi Borja,

Thanks for the response, it looks like an issue with the table being loaded via a resident load and then being dropped.

When the data is loaded in via file import this does circumnavigate the issue, however when I attempt to clear the file it then fails again. any ideas as ideally I don't want passwords lying around in an unencrypted file.

Thanks,

Chris

//This works:

STORE SecurityNew into SecurityNew.qvd (qvd); //write table to a local file

SECTION Access;

Directory;

LOAD USERID,

PASSWORD,

ACCESS,

PRODUCTKEY,

NTNAME,

OMIT

FROM SecurityNew.qvd (qvd); //import local file

//However when I add this afterwards, it then fails to load:

SECTION Application;

SecurityClear:

LOAD * inline [Cleared]; //create a blank table

STORE SecurityClear into SecurityClear.qvd (qvd); //overwrite the existing file

DROP Tables SecurityNew,

SecurityClear;

Just noticed an error with my above code - overwriting the wrong file.

Amended this to the correct file name and used a txt file and this worked (qvd still didn't work).

not an ideal solution as I'd like to use resident load instead of storing locally so if anyone has any ideas I'd appreciate it. Chris

Re: Issue with Section Access - resident load and ntname

stevedark — Mon, 16 Jul 2012 19:28:57 GMT

This may be a side effect of the way that QlikView will auto concatenate tables with exactly the same structure.

I presume that SecurityNew is in an identical structure to the Section Access table? When you do the Section Access this will then append to the existing table. When you drop Security New at the end you are at that point dropping your Section Access.

Try adding an extra dummy column to both your SecurityNew and SecurityClear tables and see if that fixes the issue.

Steve

http://www.quickintelligence.co.uk/

Re: Issue with Section Access - resident load and ntname

Mon, 16 Jul 2012 20:12:33 GMT

Hi Steve,

Thanks for the reply; I have tried your suggestion but no joy I'm afraid - here's my revised code following the changes.

Chris

SecurityNew:

LOAD USERID,

PASSWORD,

ACCESS,

PRODUCTKEY,

NTNAME as SHAZAM,

IF(ACCESS='USER','PRODUCTKEY') AS OMIT,

1 as Test

Resident SecurityRaw where (match(SecDept,DEPT,'*') or SecProd=PRODUCT) ;

SECTION Access;

LOAD USERID,

PASSWORD,

ACCESS,

PRODUCTKEY,

SHAZAM as NTNAME,

OMIT

Resident SecurityNew;

SECTION Application;

SECTION Access;

LOAD USERID,

PASSWORD,

ACCESS,

PRODUCTKEY,

SHAZAM as NTNAME,

OMIT

Resident SecurityNew;

SECTION Application;

//remove final security tables

DROP Tables SecurityNew;

Re: Issue with Section Access - resident load and ntname

stevedark — Mon, 16 Jul 2012 20:50:20 GMT

Section Access doesn't like you doing too much manipulation of the data when you bring it. Can you try doing the 'IF(ACCESS='USER','PRODUCTKEY') as OMIT,' when you are building SecurityNew, rather than when you are doing the RESIDENT load from it?

You shouldn't have to persist anything to disk to get this to work.

- Steve