16 Replies Latest reply: Jun 10, 2011 3:40 AM by mariomasciulli RSS

    Parameter in Section Access & Loadscript

      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

        • Re: Parameter in Section Access & Loadscript
          Miguel Angel Baeyens de Arce

          Hello,

           

          I don't know if I got your issue right. Section access works on reload, and reduces data when the user logs in. Taking a look at your script, it seems you want to load different data when user logs in instead, which is not possible. When the user logs in, the reduction is done on he records already existing in the document, and the user actually cannot reload the document (unless specified otherwise in the Document Security settings).

           

          That said, and according to your script, the line

           

          LET Linguaggio2 = FieldValue('LANG', 1);
          

           

          Will always return the first value of LANG. Since table Cono_dati has not been reduced in load time, and never will be, it will always return "2", which may not be correct.

           

          What you can do is add the LANG field to your datamodel, so you don't need to reload the document and when reduction takes place just after the user credentials are passed on to QlikView, it will only show those correspoding to the section access.

           

          Besides, you will need to check the "Initial Data Reduction Based on Section Access" box in the Settings menu, Document Properties so the reduction happens.

           

          You will not need to use that WHERE when the user has already reducing data when logging.

           

          Take the following example based on your script:

           

          SECTION ACCESS;
          
          LOAD * INLINE [
              ACCESS, USERID, PASSWORD, LANGUAGE
              USER,  A, A, 20
              ADMIN, B, B, 2
              ADMIN, ADMIN, ADMIN
          ];
          
          SECTION APPLICATION;
          
          TAB_UNIT:
          LOAD * INLINE [
          Field1, Field2, LANGUAGE
          1, AAA, 1
          2, BBB, 2
          3, CCC, 20
          4, DDD, 20
          5, EEE, 2
          ];
          

           

          When user B logs in, it will only see records 2 and 5, since the rest of the data is reduced. Likewise, when user A logs in, only records 3 and 4 are available. When ADMIN logs in (from the Desktop and local file, not from the Server) all data is available (he has and empty value for LANGUAGE in section access).

           

          This is how reduction happens in QlikView using section access.

           

          Hope that helps.

           

          Miguel Angel Baeyens

          BI Consultant

          Comex Grupo Ibérica

            • Re: Parameter in Section Access & Loadscript

              Hello Miguel Angel,

              first of all thanks for your reply.

               

              In the past message I forgot to say that the qvw file is scheduled to be reloaded every xx hours.

              So actually, I don't try to reduce data with user login but I correctly reduce data (but only for column UNI_BU) with the scheduled reload.

               

              I agree with your suggestion to introduce the column LANG in the model data, but this is not an easy task for the way my model is done.

              What I'm trying to understand is if I can pass LANG value (the right one) and use it in the WHERE clause of the loadscript without modifying the model data.

              Like I said in my first message:

               

              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.

               

               

              According to your experience is there a chance to pass the value of variable VAR in the WHERE clause of the loadscript?

              If not, do you have any other suggestion to solve this problem?

               

              Thanks for you patience,

              Mario

                • Re: Parameter in Section Access & Loadscript
                  Miguel Angel Baeyens de Arce

                  Hi,

                   

                  I'm assuming you are using Publisher, is that right? Anyway, the LET variable above should be enough provided there is one and only one possible value for UNI_BU or LANG for each user, and use then in the WHERE.

                   

                  Hope that helps.

                   

                  Miguel Angel Baeyens

                  BI Consultant

                  Comex Grupo Ibérica

                    • Re: Parameter in Section Access & Loadscript

                      Hi,

                      no actually I'm not using Publisher.

                      The reload of the report is done with a Windows Scheduled Task where I execute a Partial Reload of the report itself.

                       

                      I defined a variable called VAR that contains the right value of LANG depending of the user accessing.

                      But when I use it in the WHERE clause of the loadscript, $(VAR) is not evaluated and I see in the log file the string containing the corresponding expression instead of the variable value.

                       

                      I get:

                      WHERE Column = =LANG (Expression of VAR)

                       

                      instead of:

                      WHERE Column = 20 (Value of VAR)

                       

                      Thanks again,

                      Mario

                        • Re: Parameter in Section Access & Loadscript
                          Miguel Angel Baeyens de Arce

                          Hello Mario,

                           

                          Are you using the LET statement for the variable instead of the SET you were using as I mentioned above?

                           

                          Regards,

                           

                          Miguel Angel Baeyens

                          BI Consultant

                          Comex Grupo Ibérica

                            • Re: Parameter in Section Access & Loadscript

                              Hello Miguel,

                              I tried both:

                              1. when I use SET statement the behaviour is the one I described above.
                              2. when I use LET, in the query log I have no evaluation of the variable, so the WHERE clause looks like this:
                                WHERE Column =
                                And no value appears at the place of $(VAR).

                               

                              This is really strange!

                              Thanks once more,

                              Mario

                                • Re: Parameter in Section Access & Loadscript
                                  Miguel Angel Baeyens de Arce

                                  Hi Mario,

                                   

                                  FieldValue starts counting on 1. On your example above, you are using 0 instead. Are you using 1 so FieldValue with LET returns the first value for the field specified?

                                   

                                  Hope that helps.

                                   

                                  Miguel Angel Baeyens

                                  BI Consultant

                                  Comex Grupo Ibérica

                                    • Re: Parameter in Section Access & Loadscript

                                      Hi Miguel Angel,

                                      I tried your suggestion but if I use:

                                      LET Linguaggio = FieldValue('LANG','1')

                                      I got the wrong value of LANG: 2 instead of 20.

                                       

                                      Like I said in my first message, it seems like data reduction is not seen from this function and it always returns the first value in the table independently from the user who has access.

                                       

                                      Have you ever experienced this kind of problem?

                                      I think that the closest street to the solution is the one who use VAR and $(VAR), but there's something wrong...obviously!!

                                      Have you please any other suggestion for me?

                                       

                                      Thanks once again,

                                      Mario

                                        • Re: Parameter in Section Access & Loadscript
                                          Miguel Angel Baeyens de Arce

                                          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

                                            • Re: Parameter in Section Access & Loadscript

                                              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

                                                • Re: Parameter in Section Access & Loadscript
                                                  Miguel Angel Baeyens de Arce

                                                  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

                                                    • Re: Parameter in Section Access & Loadscript

                                                      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

                                                        • Re: Parameter in Section Access & Loadscript
                                                          Miguel Angel Baeyens de Arce

                                                          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

                                                            • Re: Parameter in Section Access & Loadscript

                                                              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