topic Re: help with script... in QlikView

help with script...

hopkinsc — Tue, 12 Jun 2012 15:05:16 GMT

Hi all,

Can anyone tell me whats wrong with the following script please?

It seems to load sucessfully, but does not show me the 2nd table in the table viewer

securitytmp:
LOAD * INLINE [
    ACCESS, USERID, PASSWORD, NTNAME
    ADMIN, ADMIN, PMSRQVADMIN,*
    ADMIN, PMSR, PMSRQVADMIN,*
    ADMIN, KLU00, PMSRQVADMIN,*
    ADMIN, CHRIS, 1,*
];
Concatenate (securitytmp)
LOAD upper(NTNAME) as NTNAME, 
     upper(ACCESS) as ACCESS, 
     USERID, 
     PASSWORD, 
     COUNTRY,
     PROJECTCODE
FROM
C:\Subsea7\QVDev\PMSRUserRights\UserRights.xlsx
(ooxml, embedded labels, table is USER)
Where COUNTRY > 0;
security:
LOAD *
Resident securitytmp;

It all looks ok to me, thanks for helping

Re: help with script...

Miguel_Angel_Baeyens — Tue, 12 Jun 2012 15:12:02 GMT

Hi,

That piece of script seems to be all right, but do you have before or after that any "Section" statement? That may cause to only show some of the values. Did you uncheck the "Initial reduction based on section access" and "Strict Exclusion" in Settings menu, Document Properties, Opening tab when testing the document to see whether or not the table was working fine?

And apart from that, note that all fields al values, not only NTNAME and ACCESS must be uppercase.

Note that QlikView concatenates automatically when two tables have the same number of fields and these fields have the same names, so this is likely to work:

security:
NOCONCATENATE LOAD *
Resident securitytmp;
DROP TABLE securitytmp;

You will need to DROP the table "securitytmp" if you don't want to create a huge synthetic key.

Hope that helps.

Miguel

Re: help with script...

hopkinsc — Tue, 12 Jun 2012 15:48:40 GMT

Hi miguel,

Sorry, i'm not thinking straight today! I completely forgot that Qlikview automatically concatenates, which is the reason i am missing a table.

Sorry.

While your hear though... if i had an excel document full of different user names, passwords, countries and project codes, how would i let a particular user see all project codes without having to list them all against his/her user name. ?

I thought i could use * in the excel document, but it doesn't seem to work for me. instead it lists * as a possible value..

Re: help with script...

Miguel_Angel_Baeyens — Tue, 12 Jun 2012 16:09:03 GMT

Hi Chris,

In order to make the "*" equal to all possible values, you need that at least one of your users have all possible values listed, otherwise, that will not work. Note that QlikView only allows one value per line so, if there are 100 project codes, at least one user (any admin, likely) must have 100 rows with his account information and each of the 100 possible values for project code.

This way, you may use the * to mean "all values".

Hope that helps.

Miguel

Re: help with script...

hopkinsc — Tue, 12 Jun 2012 16:12:02 GMT

Ok great, thanks for your help Miguel. I'll try that out..