I have a question regarding a security/modelbuilding issue:
I have a Qlikview-file with some tables (see below)
SALES_ABC is the fact-table, the other ones are dimension-tables.
My problem is that the user logged in (found in USRS-table) should just see the data in the fact-table depending on their "security-level" found in the two fields CUSTOMER_ATTRIBUTE04_ID and SEC_VALUE1. When Opening the file the data is reduced based on section access.
The link for CUSTOMER_ATTRIBUTE04_ID selects the right customer categories, works OK.
The problem is the field SEC_VALUE1 the should select the right Product groups via PRODUCT_ATTRIBUTE01_ID, but if I name the field in USRS to PRODUCT_ATTRIBUTE01_ID instead of SEC_VALUE1 Qlikview cannot load the data, probably due to some circular links.
My solution is now using Macros. It nearly works OK, but if anyone have another solution, please feel free to post that one instead.
The problem with the macro-solution is reading the SEC_VALUE1 field. I'm not that used to VBScript and must say that the APIGuide isn't that easy to understand either. My thoughts was that the method GetPossibleValues for the field SEC_VALUE1 should give me just one value due to the fact of Reduced data on load, but it still gives me all values.
One more thing that I have to mention:
The file is running on a Qlikview server with 20 different users (i.e different security levels) and they are also using AJAX instead of the IE Plugin.
Version used is v9.00 SR2
Anyone having a better solution or at least can point me in the right direction reading the SEC_VALUE1 for the logged on user?
If I understood your question correctly, SEC_VALUE1 and PRODUCT_ATTRIBUTE01_ID contain the same type of values (so that matching is appropriate)? If that's the case, instead of using a macro, have you tried doing this with an IF statement in your expressions? I think with the kind of calculations you need you can do it and not even lose much on performance. So it would look like this:
if(PRODUCT_ATTRIBUTE01_ID = SEC_VALUE1, calculate formula)
Thanks for your answer.
The only thing I want to do is select the Products with PRODUCT_ATTRIBUTE01_ID = SEC_VALUE1 if SEC_VALUE1 is not equal to '*', in that case all Products should be shown.
By selecting Products for a specific SEC_VALUE1 it would be best if the non-selected ones are hidden/reduced for that user.
I might be stupid, but I don't really understand your offered solution. But I see that I wasn't so clear with my question either ;-)
Let me try to explain it better in that case.
I've a USRS-table with users using section access and nt-authentication to set up the security.
Per User I've 2 fields (CUSTOMER_ATTRIBUTE04_ID and SEC_VALUE1) that can have either * (a star) or an ID that should select rows from the fact-table for:
a) CUSTOMERS included in the group with ID = CUSTOMER_ATTRIBUTE04_ID (or all customers if ID = *)
b) PRODUCTS included in the product-group with ID = SEC_VALUE1
The a) case is solved automatically by normal QV-links betweem fields with same names in different tables.
Case b) cannot be solved that way because of "circular references for links" (or whatever, see picture in my first post)
Therefore I tried to solve this problem with a macro reading the value for SEC_VALUE1 for the logged on user, but I was unable to do that, due to my lack of experience in both macro and/or VBScript.
The best solution would be not using macros at all, but I cannot get the databasemodel to work (be loaded) into QV the way I want it to, and therefore I tried the macro approach.
So, my main problem is easy, how to read the value of the field SEC_VALUE1 found in the USRS-table for the logged on user using macro?
Hope that makes my problem more understandable without messing everything up ;-)
OK, I think I understand now why you can't (or shouldn't) do this with IF statements. For something like this you would probably need a nested IF, which would crank up the memory required. I think this should work to get the current Windows identity in VBScript:
Set objNetwork = CreateObject("WScript.Network")
strUserName = objNetwork.UserName
Then just compare this field to your SEC_VALUE1. Hope this helps.
Find out the user shouldn't be hard, I can use the function OSUser to do that.
My problem is to select the right SEC_VALUE1 from the table USRS.
My code looks like this right now (remember that I'm a newbee on macros ;-)
Set q = ActiveDocument
Set secval = q.Fields("SEC_VALUE1").GetPossibleValues
If secval.count=0 then
sval = secval.Item(0).Text
Set lb = ActiveDocument.GetSheetObject("lbAO")
Set cp = lb.GetProperties
if cp.Layout.HideExcluded = true then
cp.Layout.HideExcluded = false
if secval = "ResNF" then
cp.Layout.HideExcluded = true
If secval = "ResFG" then
cp.Layout.HideExcluded = true
It is the code for getting the right value to the variable secval that gives me problem.
How can I "filter" the values found in USRS to just give me the value for the logged on user?
Section access uses the NTSID value to get the right user, and that value can also be found in the USRS-table. But I haven't found a way to get that value so that I can use it to select the corresponding user in the USRS-table, and that way get the right SEC_VALUE1...