10 Replies Latest reply: Apr 13, 2016 8:30 PM by Lyn A RSS

    Select multiple values EXCEPT one

    Lyn A

      Hi All,

       

      I have problems with my current security setup. I have a list of countries and since we added a new country recently, this new country is limited only to selected users.

       

      ex.

        

      USER_GROUPCOUNTRY
      A*
      BSG
      BJP
      BAM
      CJP

       

      My country lists up to 30+. Since it's a big list, It's not really nice to list everything except the new one. Is there any solution for this?

       

      Thanks!

        • Re: Select multiple values EXCEPT one
          Maxim Senin

          Hi,

           

          Not sure I understood your requirement, but in Section Access you can list those values which your selected users should have access so the other ones won't have an access to them.

           

          Best regards,

          Maxim

            • Re: Select multiple values EXCEPT one
              Lyn A

              Hi Maxim,

               

              Thanks for replying, Since users that have access to all countries I just put * to the field for them to access all countries, problem is, an additional country was recently added and selected users are only allowed to see this new country. Since the list is so long, I don't want to put it one by one per each grouping.

               

              ex.

               

                

              USERGROUPCountry
              A*


              to

              USERGROUPCountry
              ASG
              PH
              AM
              JP
              and so on

               

              I don't want to list all, just because of an additional country.

            • Re: Select multiple values EXCEPT one
              Peter Cammaert

              Actually, Section Access (almost - there are some exceptions) follows the same rules as those that apply to your data model: associations and linking in general are only done on strictly specififed values, never on things like "all of them", or "everything except these two".

               

              You can however simulate your own link value handling by defining an external security definition source (which is always better to make it manageable without requiring you to modify your hidden script) and adding things like include/exclude columns, or to handle self-created concepts like '*'.

               

              The code that loads this external data and translates into a boring SA access list will become a bit more complex than a simple INLINE LOAD.

               

              Peter

              • Re: Select multiple values EXCEPT one
                Amit Saini

                Hi,

                 

                In general new row is required to assign user to different country , but what may be you can try to group these company name ....Could be create Hierarchie based on countries as regions like Asia , North America , Europe.....and than give section access on Hierarchie level.

                 

                Thanks,

                AS

                • Re: Select multiple values EXCEPT one
                  sujeet singh

                  Use a excel sheet to have the security mapping and call it in SA script. Hence when ever you need to add the new one , just add in the sheet.

                    • Re: Select multiple values EXCEPT one
                      Lyn A

                      Hi,

                       

                      Actually I'm using an excel file for my security... the problem is that if I list the countries one by one by each group loading the binary files takes long.. my records of 600k jumped to 11m because of the combinations I have for my security.. I want to lessen  this number if there is any solution for this.

                        • Re: Select multiple values EXCEPT one
                          Peter Cammaert

                          You do not have to list every country on a single line. As an example (not using an Excel but instead an INLINE LOAD, just to make my point), what about this trick?

                           

                          Section Access;

                          LOAD * INLINE [

                          ACCESS, USERID, GROUP

                          ADMIN, SA,

                          USER, ME, A

                          USER, YOU, B

                          USER, HE, C

                          ];

                           

                          Section Application;

                          // Copying entries in this table is extremely simple

                          RawSAGroups:

                          LOAD * INLINE [

                          GROUP, GroupSpec

                          A, SG;PH;AM;JP

                          B, IN;SA;JP;SK;NZ;TH;LA;VN;

                          C, NewCountry

                          ];

                           

                          GroupTable:

                          LOAD GROUP, SubField(GroupSpec, ';') AS Country

                          RESIDENT RawSAGroup;

                           

                          DROP Table RawSAGroup;:

                           

                          I'm referring to my first post: use QlikView script statements to make your security easier to manage.

                           

                          I don't understand why anything should grow from 600k rows to 11m rows. Can you explain why this happens?

                           

                          Peter

                            • Re: Select multiple values EXCEPT one
                              Peter Cammaert

                              This can be made even shorter, especially if you don't like part of your SA information to reside outside of Section Access;

                               

                              Section Access;

                              SA:

                              LOAD * INLINE [

                              ACCESS, USERID, GROUP

                              ADMIN, SA,

                              USER, ME, A

                              USER, YOU, B

                              USER, HE, C

                              ];

                               

                              // Copying entries in this table is extremely simple

                              LEFT JOIN(SA)

                              LOAD GROUP, SubField(GroupSpec, ';') AS COUNTRY INLINE [

                              GROUP, GroupSpec

                              A, SG;PH;AM;JP

                              B, IN;SA;JP;SK;NZ;TH;LA;VN;

                              C, NewCountry

                              ];


                              Section Application;

                              // Section Access will link to a data model field called 'COUNTRY'. Add

                              // more fields to SA if they all link to different data model tables.

                              // Otherwise create a composite key.

                              :

                               

                              Best,

                               

                              Peter