4 Replies Latest reply: Dec 5, 2013 4:48 PM by Bill Britt RSS

    Section Access with multiple Fields and a range of Values

    Christian Klinge

      Hello Com,

       

      i´ve got a small problem using section access. I´ve got a table for section access with the following structure:

       

      NTNAMEID1ID2ID3 fromID3 to
      User1A1110009999
      User1A5009999
      User1B2245005000
      User2A1109999

       

      Is there any possibility to get the from/to range in section access.

       

      Thank you in advance

        • Re: Section Access with multiple Fields and a range of Values

          Hi Christian

           

          I would recommend you break down the 'From' and 'To' to provide a more granular access table.  This is a relatively simple task.  You will need the following code which firstly loads your source table, then creates a second table which lists out each of the different values.

           

          StartData:

          LOAD * INLINE [

              User, ID from, ID to

              A, 1, 100

              B, 30, 50

              C, 50, 60

              D, 1, 7

          ];

           

          Final:

          Load

              *,

              [ID from]+Iterno()-1 as ID

             

          Resident [StartData]

           

          While ([ID from] + IterNo() -1) <= [ID to];

          Drop Table StartData;

           

          Once you have your final table, load this into Section Access via a resident load and drop the table (Final:) to ensure confidentiality of access rights.

           

          Let me know how you get on.

           

           

          Steve

            • Re: Section Access with multiple Fields and a range of Values
              Christian Klinge

              Hi Stephen,

               

              this might be a possible solution, had something simillar in my mind. The problem is the size of the table with the right infomation. There are around 4.700 different users who can have multiple rights in every ID, with almost every combination of ranges in ID 3, so that the source table has got something around 2.5 mill. lines. If I would now use this idea, the tablesize will get enormous and I think it won´t have a good performance.

                • Re: Section Access with multiple Fields and a range of Values

                  Christian

                   

                  The first thing to note, is that although the Section Access table will be large, most of the records are dropped when a user opens the file (as I have been taught).

                   

                  I'm not sure if Qlikview supports using more complex Section Access beyond having such a simple (and therefore, large in your case) table.

                   

                  There are pieces of code that spring to mind like intervalmatch and possibly an inline table to associate each ID3 to a certain group, but the issue is you have a many-to-many relationship between the ID3 and the bands, i.e.;

                   

                  ID3 4567 falls into bands 0 -9999 and 4500-5000  and of course there are multiple ID3's in each band.

                   

                  Is there a way to group partial bands?  By this I mean, can you group say 500 ID3's together?  So that in the fact table you could create a field called BAND and for a record, e.g. 4567 associate it to BAND value 4500-5000.  This could be then used to drive your section access, so in stead of 500 records for multiple users, they would one get one record?

                   

                  This does mean that users do not have very distinct bands such as 3452-8538 etc.  Does this situation occur?

                   

                  Let me know your thoughts and any further information you may have?

                   

                   

                  Steve

              • Re: Section Access with multiple Fields and a range of Values
                Bill Britt

                Hi Christian,

                 

                You will have to do something like this.

                 

                section.png

                 

                Bill