Section Access Loading from SQL

[#1]

Hello,

I'm trying to use section access in my QV app. I first tried to load the user security from an excel file and it worked fine. Then I tried to use to load from a store procedure and it didn't work. I mean I was able to load the data with no problem but if I saved the document and closed it, then the next time I tried to open it I was not able to do so. It said access denied to this QlikView document. I then tried to make it simpler and load from a SQL table which contains the exact same data as the excel sheet. However, this did not work either and I ended up with the same problem as using the stored procedure. I don't understand what makes this so different. I have copy pasted data from my SQL table into Excel sheet and it load fine from Excel sheet and I can open the file again and again with no problem. Here is what I have in my load script for section access when loading from excel:

SECTION Access;

LOAD CustNo as [CUSTOMER NO],
     UserLogon as NTNAME,
     [ACCESS]
FROM CustList.xlsx
(ooxml, embedded labels, table is CustAccess);

And This is what I have when trying to load from SQL table

LOAD CustNo as [CUSTOMER NO],
     UserLogon as NTNAME,
     [ACCESS];
SQL SELECT *
FROM ARCTEMS.dbo.QVCustAccess;

The content of my table is as follows:

CustNo	UserNo	UserLogon	ACCESS
168581	104139	AM\baxtebjc	USER
169363	104139	AM\baxtebjc	USER
*	*	AM\backupexec	ADMIN
*	*	AM\niavarna	ADMIN

Any help would be greatly appreciated. I have also tried using Trim() around my fields just in case there are spaces or things like that interfering but that did not help either.

SECTION Access;

[#2]

You can try debugging it by loading the user table normally without section access. Check and see whether the user table can link to your other tables.

[Anonymous]

Make sure that you load in UPPERCASE from the sql server.  For some reason its neaded when you load from a SQL server opposite an inline table.  You can also read the PDF document regarding section access, there is an SQL example in it.  Henrik

[#4]

Thanks for the responses. I did load it without the section access and I see that the user table does link properly to one other table. I also checked the content of the table in both senarios and the table has the exact same content.

I tried loading with upper function like this:

LOAD UPPER(CustNo) as [CUSTOMER NO],

     UPPER(UserLogon) as NTNAME,

     UPPER(ACCESS) as ACCESS;

SQL SELECT *

FROM ARCTEMS.dbo.QVCustAccess;

And this still did not make a difference. I'm still locked out and can not open the file after a reload and save.

Any other suggestions?

[Miguel_Angel_Baeyens · Accepted solution]

Hi,

My guess is that Excel converts CustNo and UserNo values to numeric values, but in your database or model they are strings, or viceversa. Give a try to something similar to the following:

SECTION ACCESS;
LOAD UPPER(Text(CustNo)) as [CUSTOMER NO], // Text() in case they are text or Num() should they are numeric
     UPPER(UserLogon) as NTNAME,
     UPPER(ACCESS) as ACCESS;
SQL SELECT *
FROM ARCTEMS.dbo.QVCustAccess;
SECTION APPLICATION;

You will see that easily creating a listbox with CustNo field. If it's left aligned, they are strings, if it's right aligned it's numeric.

Hope that helps.

Miguel Angel Baeyens

BI Consultant

Comex Grupo Ibérica

[#6]

Perfect. That worked. I just added a NUM() around [Customer No] and that did the trick. Thank you very much for your help!

[Miguel_Angel_Baeyens]

Hi,

For the sake of clarity, if your value is numeric, then you don't need to use the Upper() function on that field. Don't know if you already did the change but I think it's worth noting.

LOAD Num(CustNo) as [CUSTOMER NO] ...

Regards.

Miguel Angel Baeyens

BI Consultant

Comex Grupo Ibérica