Parameter in Section Access & Loadscript

[#1]

Hi to everybody,

in the last few days I'm having this problem and I really don't know how to figure it out.

I have my qvw file with Section Access where users are defined with Windows Single Sign On Account.

In the Section Application I defined a table with two columns (UNI_BU and LANG) who execute data reduction.

The first one is related to other columns in the model data (so actually it goes in join with these columns) and it works fine, executing the right data reduction according to the user who has access to the system.

The second column is a parameter that I want to use in the loadscript in a where clause.

I tried two different approaches to the problem:

1. I defined a variable (let's call it VAR) who catch the value of LANG according to the user who had access.
   But if I want to use $(VAR) in the Loadscript I don't get any result in the query. I checked the log and I saw that the variable is replaced with corresponding text ("=LANG").
   I also tried to use antoher variable so defined: SET VAR1 = $(VAR). VAR1 keeps the right value, but still I can't use it within the where clause.
2. I tried to define a variable using fieldvalue('LANG',0) function applied to the reduction table. My hypothesis was that, since I access with my own Windows SSO account, this function would have returned to me just one row with the LANG value of my account.
   But I noticed that the value returned is always the first one in the table independently to the access executed.

I attach a qvw file with the example of the structure over explained.

Can please anyone help me?

Thanks in advance,

Mario

[Miguel_Angel_Baeyens]

Mario,

Are you checking the "Initial Data Reduction..." in the Document Properties, Opening?

If you log in to your document as one of the reduced users, what values do you see?

Is it possible again that for one user there may be more than one languages?

Regards,

Miguel Angel Baeyens

BI Consultant

Comex Grupo Ibérica

[#3]

Hi Miguel,

the "Initial Data Reduction Based on Section Access" option is correctly checked in my document.

In fact, using a static value for LANG, when I log on to the document I correctly see the part of the dataset of my competence.

So data reduction based on the column UNI_BU works fine to me.

When I try to personalize LANG value for the user who access the document, I get the problems I described you before.

Theoretically a user could have more than one language specified, but in this case I defined one and only one language for every user.

And when I log on to the document like user A, I expect to see the LANG value 20 as specified in the table Cono_dati and not the resulting value 2.

Thanks again for your geat patience!

Mario

[Miguel_Angel_Baeyens]

Mario,

If there is a possibility of one user having more than one languages, then that's the problem, since the FieldValue() needs as second parameter the number of the value you want to retrieve.

Anyway, you can do something like the following to get a list of all possible languages for any given user, so you can pass it later to the WHERE condition using the IN clause in the SQL statement.

PossibleLanguages:
LOAD chr(39) & Concat(DISTINCT LANG, chr(39) & chr(44) & chr(39)) & chr(39) AS PossibleLangs
RESIDENT Cono_dati;
LET vLangs = FieldValue('PossibleLangs', 1);
DROP TABLE PossibleLanguages;

Now vLangs will store something like

'1', '2', '20'

And you can use it in the WHERE as you were trying

//First case
TAB_UNIT:
LOAD *; 
select *
from UNIT U
where U.LANGUAGE IN ($(vLangs)); // You may need to check quoting to get the right values properly formatted

Hope that helps.

Miguel Angel Baeyens

BI Consultant

Comex Grupo Ibérica

[#5]

Sorry Miguel,

maybe my last answer has been a little bit confusing.

When I say that theoretically a user can be related to more than one value of LANG, I mean that the application gives this chance but in this moment every user has one and only one value of LANG related.

So, if I try to follow your last suggestion I'll always have all values of LANG ('1','2','20') independently from the user who has access to the qvw file.

In this moment I think I don't have other chance than looking for a workaround.

But it's very strange that actually I'm not able to pass to a WHERE clause of the loadscript the value of a variable defined in the document...

Any other suggestion will be very appreciate!

Thanks in advance Miguel.

Mario

[Miguel_Angel_Baeyens]

Hello Mario,

Maybe using a LookUp() function instead of the FieldValue() would do. Anyway, I keep missing your point. If data is reduced, then not all values in LANG are going to be available, because it will depend on the user that is reloading. Something diferent is that the reloads are done always by the same NT user (that's my guess now, the one has scheduled the task in the OS), in this case, all the above is useless.

I'm thinking of LookUp() because you cannot get always the first record, since the first record may not be the correct one. So how to know what record corresponds to what user? Probably using numeric users instead of alphanumeric, and taking that number as the second parameter in the FieldValue() function.

How does QlikView know what user is reloading? Are you prompted every time it reloads?

Miguel Angel Baeyens

BI Consultant

Comex Grupo Ibérica

[#7]

Hi Miguel,

thank you very much for your indication.

I'm very close to find the solution, because I verified that LOOKUP function is the one for me.

I mean:

LET vLang = lookup('LANG','GRUPPO','A','Cono_dati');

returns to me the correct value of LANG for the user related to GRUPPO 'A'.

LET vLang = lookup('LANG','GRUPPO','B','Cono_dati');

returns the correct value of LANG for users of GRUPPO 'B'.

The last step is to parametrize the call to the lookup function.

I use a variable called GROUP, whose expression is ('=GRUPPO').

GROUP contains the right value of LANG for the user: so this can be A or B etc.

Actually I'm not able to pass the value of GROUP into lookup function.

I tried with:

LET vLang = lookup('LANG','GRUPPO',$(GROUP),'Cono_dati');

or

LET vLang = lookup('LANG','GRUPPO','$(GROUP)','Cono_dati');

but none of this gives me back 'A' or 'B' etc.

Have you please any suggestion for me?

Thanks again,

Mario

[#8]

Up!!