topic Re: Section Access from Excel in QlikView

Section Access from Excel

Tue, 31 Dec 2013 16:13:56 GMT

I have read the requirements for section access to be upper, but that hasn't seemed to solve my problem.

below is my section access script:

Section Access;

LOAD * INLINE [

USERKEY, ACCESS, USERID, PASSWORD, NTNAME, %SECSOURCE

1,ADMIN, , ,DBG\CAMARK,*

2,ADMIN,ADMIN,ADMIN123,*

];

REM LOAD * INLINE [

USERKEY, OMIT

1, SECURITY_ID

1, CUSIP

1, DEAM_ID

];

LOAD upper(USERKEY),

upper(OMIT)

FROM Include\Security.xlsx

(ooxml, embedded labels, table is [Field Omits]);

Section Application;

LOAD * INLINE [

%SECSOURCE, SEC_SOURCE_ID

4,4

1,1

26,26

];

The second load (from the excel file), has the exact same data in the excel that it does in the REM'd INLINE statement. The fields are appropriately omitted when using the inline, but not when referencing the table from the excel. If I put the same load from excel statement after Section Application, i see the data in the data model, so QV does seem to be able to access the excel file correctly.

Re: Section Access from Excel

swuehl — Tue, 31 Dec 2013 16:25:12 GMT

Try

LOAD upper(USERKEY) as USERKEY,

upper(OMIT) as OMIT

FROM Include\Security.xlsx

(ooxml, embedded labels, table is [Field Omits]);

Re: Section Access from Excel

Tue, 31 Dec 2013 16:28:13 GMT

unfortunately no, that didn't resolve the issue. I understand where you are going with that, but alas.

Re: Section Access from Excel

Peter_Cammaert — Tue, 31 Dec 2013 17:21:47 GMT

A difficult one. Must be data related.

Have you tried adding Trim() inside the calls to Upper()?

Peter

Re: Section Access from Excel

Tue, 31 Dec 2013 17:25:57 GMT

trim didn't help

Re: Section Access from Excel

Peter_Cammaert — Tue, 31 Dec 2013 17:40:54 GMT

If you put the Excel LOAD after Section Application;, does reduction work as expected? That would be proof of association and reduction working as expected, but not the OMIT phase.

Peter

Re: Section Access from Excel

Tue, 31 Dec 2013 17:44:47 GMT

there's nothing for reduction to reduce, but it does load the table. (if it's after the section application line)

Re: Section Access from Excel

Peter_Cammaert — Tue, 31 Dec 2013 17:48:48 GMT

Just add a few lines to your Excel table, for instance with USERKEY=5 and see if those get reduced away.

Peter

Re: Section Access from Excel

Tue, 31 Dec 2013 17:52:53 GMT

ahh, I see; but it didn't reduce the userkey=5 do these links need to be hidden with %s?

Re: Section Access from Excel

Tue, 31 Dec 2013 17:57:14 GMT

appologies, it is working. I had broken my test document and forgot to reinstate the reduction in the document properties.

Re: Section Access from Excel

Tue, 31 Dec 2013 17:59:29 GMT

wow, and with that discovery, the problem is fixed. oi, stupid wild goose chases

Re: Section Access from Excel

Peter_Cammaert — Wed, 01 Jan 2014 12:39:48 GMT

Won't happen again in 2014 I guess.

Happy new year,

Peter