4 Replies Latest reply: Sep 3, 2013 12:03 AM by prabhu appu RSS

    Null value in the Section application reduction field

      section access fields :

       

      ACCESSUSERIDCITY
      ADMINMAIN*
      ADMINMAIN
      USERUSER_1X
      USERUSER_2Y

       

      section application fields :

      CODECITYsales
      AX100
      BY200
      C 300

       

      using the above fields in a qlikvies report , i want user 'MAIN' to be able to view the data belong to CODE 'C' for which the CITY is null.

      Note - CITY is the data reduction field

       

      Can anyone help me please..

       

       

      Regards,

      Sankar

        • Re: Null value in the Section application reduction field
          Jim Gay

          I have a similar problem.

           

          My fact table is limited on a field called USERID.  Data reduction works well with my current setup, however I have added some summary records to my fact table that I identify using a new field called RecordType.  The detail rows are RecordType = 'D' and the summary records are 'S'.  The summary records have a NULL value in the USERID column so I can summarize the facts by manager and organization so each employee can see the overall averages and totals for their manager and organization.

           

          Section Access:

          ACCESS, NTNAME, USERID

           

          Facts:

          USERID, MANAGERKEY, DIRECTORKEY, DEPT, DIVISION, NUMERATOR, DENOMINATOR, RECORDTYPE

           

          The problem I have is the Summary records are being eliminated by the data reduction.  Is there a way to keep these records in the data set with a blank or NULL USERID column?

           

          I have found it very difficult to find any information on keeping summary data when using section access.

           

          Any help would be greatly appreciated!

            • Re: Re: Null value in the Section application reduction field
              prabhu appu

              Hi Sankar,

              As per your section access table admin user can able to see all the data.

              normally

              1) if you put * in Reduction field then the admin user can able to view all the values listed in the section access table (example1.qvw)

              2) if you put "null" then the admin user can able to view all the values in that particular field (including the values not listed in the section access table). Only in the case where the reduction field doesn't have any null value (example2.qvw).

              In your case City has null value. So the values X, Y and Null will be visible for the ADMIN user (example3.qvw)


              If you want the admin users to see only CODE C data. Then remove the ADMIN user entry with * in the section access table (example4.qvw)

               

              =====

               

              Hi Phcaptjim

              Do you want the summary records can be viewed by all the users?

              If yes create a link table wuth two fields USERID and RECORDTYPE

              USERID RECORDTYPE
              A            S
              B            S
              C            S

               

              Now All users can see summary records

               

              I have attached a file for reference Section Access_Consolidated.qvw.


              regards
              PRABHU APPU

                • Re: Re: Null value in the Section application reduction field
                  Jim Gay

                  Thanks for the response Prabhu.

                   

                  I love your example but is the synthetic key going to be an issue with this current data model?  Below is the data model for my application, it's a little more involved than the example I posted but the concept is the same.  EPMFacts stores my details and summary records with the RecordType column used to distinguish between the two.  SECURITYID is the field that links to the Section Access table for each user from the Security table.  I could add two records for each user in the security table (one for RecordType 'S' and one for 'D' but then a synthetic key would be created between the two tables.

                   

                  Do you know how I should handle this?

                   

                  (I have no idea why the image shows the calendar table twice.....that is odd)

                   

                  epm_model.png

                    • Re: Re: Re: Null value in the Section application reduction field
                      prabhu appu

                      Hi Phcaptjim,

                      This could be solved by using "generic keys". I have done it for a simple scenario. Modify this as per your requirement.

                      Create a new table with two fields GEN_SECURITYID, SECURITYID

                      Capture.PNG.png

                      and instead of SECURITYID in the fact table use GEN_SECURITYID. Change the GEN_SECURITYID for the summary records as ANY.

                      Capture.PNG.png

                      The section access table will look like this

                       

                      * INLINE [
                          ACCESS, USERID, SECURITYID
                          USER, A, 1
                          USER, B, 2
                          USER, C, 3
                          ADMIN, X, *
                      ]
                      ;

                       

                      Now any user will be able to see the summary record. (Refer the attachment for more)

                       

                      Regards,

                      PRABHU APPU