Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi,
I'd like to add section access through Data base.
Section Access;
USERADMIN:
LOAD
(Field1),
Field2,
Field3,
Field4,
UPPER( Field5) as CODE,
Field6
FROM
...\USERADMIN_ROLE.QVD (qvd);
SQL SELECT
"Field1",
Field2,
"Field3",
"Field4",
UPPER( "Field5") as CODE,
Field6,
"Field7",
"Field8"
FROM
.........";
Section Application;
I want to restrict user based upon CODE and Field5 in SQL DB.
So if a user has a CODE=500, he should only see data related to 500 and all other fields should not appear to him.
I'm loading the USERADMIN table from a qvd and using it in Section Access and I'm using SQL querry as well.
I also want to load a Inline table as I want to add a single user as an Admin, which I cannot do in Data Base. All other users are available in Data base with a particular Code to them.
I tried loading in many different ways and changing the querry in qlik but still doesn't work.
So with a qvd, Inline & SQL, how can I apply section access?
Please tell me what wrong I'm doing here?
Thanks.
Now it's my turn to get confused.
My original post was explaining the basic steps to create a simple table, unrelated to Section Access because you first have to get your data right. Only then can you apply section access. If you follow that practice, debugging eventual mistakes will become much easier. Even for me, Section Access never works perfectly the first time.
The code in Section Access uses the exact same techniques and statements as any other code. If you want to create an authorizations table, composed of rows from a QVD and rows from a SQL Database, do as follows (I'll try to use code from your other post):
Section Access;
CONNECT TO . ... ; //Connect to your SQL database. Omit if the connection is open.
USERS: // Name doesn't matter
LOAD USERNAME
ACCESS
UPPER(CODEID) as CODEID // This is the actual link field?
ROLENAME // What is this used for?
FROM Extract.qvd
CONCATENATE(USERS)
SQL SELECT
USERNAME
ACCESS
UPPER(CODE_ID) as CODEID
ROLE_NAME
FROM SomeDatabaseTable ;
Section Application;
:
Now check the connected table in Section Application, make sure the proper fields and values are available, and reload this script in a copy of your original document, never in the document itself. Simply because you may risk locking yourself out of your document and you'll need to start over. If you do get errors, or your document doesn't work as expected, please post back to this thread.
Another tip: If you didn't understand my explanation the first time, you may be lacking basic scripting skills. I would advise against starting with Section Access before you master regular load script techniques, as Section Access can be one of the most tricky and treacherous parts of QlikView development.
Start with creating a RESIDENT table called USERADMIN with a single record loaded from an INLINE spec. That should be the ADMIN user, or the user that does the reloads on your server.
Then CONCATENATE LOAD all QVD entries you need to that first table.
Then CONCATENATE LOAD all DB entries you need to that expanded table. Yes, you can add the QlikView CONCATENATE keyword to a SQL SELECT statement. See here: Concatenate ‒ QlikView
If the fields all have the same names, you will end up with a single table with authorizations from the three sources.
Best,
Peter
The field names coming from DB query is different compared to field names in Qlik.
I changed the required name Field 5 from Qlik to Code
and In DB the Field 5 name is actually Field 10 (example)
So I just want to restrict it based on CODE
I did not understand what you explained...
Can you show an example script?
Thanks.
Anyone?
Very limited expertise in Section Access, besides you are getting help from one of the best, I am sure pcammaert will respond back to any of your queries here
Thanks.
Peter's reply was confusing with no example. And it is hard to understand when I also do not have much experience in section access based on data base.
Hey, Would you describe more. I am confusing here. You've load data from qvd and then SQL. What is the use of Sql here (I mean Any reason to take those). If yes, Provide me that
Meantime, HAve a look this about your QlikView Section Access Examples
- ANIL
Hi loveisfail,
I have a table loaded from DB specially made for section access
In those fields are
Extract query:
SQL Select
USERNAME
ACCESS
CODE_ID
ROLE_NAME
From......
Store into.... as .qvd
The next step I did was I transformed this qvd into
USERS:
Load
USERNAME
ACCESS
CODEID
ROLENAME;
From... Extract.qvd
Third step I did is I have a data model where all transform qvd's are loaded but I did not add the USERS.qvd to my data model, instead I added into my source file where I'm taking binary load from my data model.
In my source file I'm trying to achieve section access by loading this USER.qvd and also writing a SQL select statement
Ex:
Section Access;
USERS:
Load
USERNAME
ACCESS
UPPER(CODEID) as CODEID
ROLENAME;
From... Extract.qvd
SQL Select
USERNAME
ACCESS
UPPER(CODE_ID) as CODEID
ROLE_NAME
From....
Section Application;
So I have a field name CODEID as stored in a different qvd which is BUSINESS.qvd and I made a same table name with same IDs inside CODEID in SQL.
I'm trying to match that query with SQL query and apply section access. Can you tell me what wrong I'm doing here?
I also used UPPER(num(CODEID) as CODEID thinking as those are numeric values.
For now I do not want to use Inline table, just from DB restrictions.
Thanks
Now it's my turn to get confused.
My original post was explaining the basic steps to create a simple table, unrelated to Section Access because you first have to get your data right. Only then can you apply section access. If you follow that practice, debugging eventual mistakes will become much easier. Even for me, Section Access never works perfectly the first time.
The code in Section Access uses the exact same techniques and statements as any other code. If you want to create an authorizations table, composed of rows from a QVD and rows from a SQL Database, do as follows (I'll try to use code from your other post):
Section Access;
CONNECT TO . ... ; //Connect to your SQL database. Omit if the connection is open.
USERS: // Name doesn't matter
LOAD USERNAME
ACCESS
UPPER(CODEID) as CODEID // This is the actual link field?
ROLENAME // What is this used for?
FROM Extract.qvd
CONCATENATE(USERS)
SQL SELECT
USERNAME
ACCESS
UPPER(CODE_ID) as CODEID
ROLE_NAME
FROM SomeDatabaseTable ;
Section Application;
:
Now check the connected table in Section Application, make sure the proper fields and values are available, and reload this script in a copy of your original document, never in the document itself. Simply because you may risk locking yourself out of your document and you'll need to start over. If you do get errors, or your document doesn't work as expected, please post back to this thread.
Another tip: If you didn't understand my explanation the first time, you may be lacking basic scripting skills. I would advise against starting with Section Access before you master regular load script techniques, as Section Access can be one of the most tricky and treacherous parts of QlikView development.
You might require to use Section Access
T1:
Concatenate(T1)
T2:
Then, Coming to this part
I also used UPPER(num(CODEID) as CODEID thinking as those are numeric values.
If you are thinking about Numeric values, Then how numeric values came for UPPER
Just use Evaluate(Num(CODEIS)) as CODEID