13 Replies Latest reply: Jun 30, 2016 12:18 PM by sujith madhavan RSS

    Section access :Access Denied

    shweta gupta

      Hi All,

       

      I am working on Section Access in Qlik Sense for the first time but everytime I am getting Access is denied.

      -Even admin is getting the same.

      -App is published in Everyone stream.

       

      I have uploaded excel sheets for both Section Access and Section Application , following is the code for the same.

       

      SECTION ACCESS;

      LOAD

          Upper([ACCESS]) as ACCESS, // Took

          Upper([USERID])as USERID,

          Upper ([PASSWORD])as PASSWORD,

          UPPER([CATEGORY]) as Sales.Category;

      LOAD

          [ACCESS],

          "USERID",

          "PASSWORD",

          CATEGORY

      FROM [lib://Folder/Section Access.xlsx]

      (ooxml, embedded labels, table is Section_Access);

       

       

      Section Application;

      LOAD

          Upper(Category) as Sales.Category,

          Product

      FROM [lib://Folder/File.xlsx]

      (ooxml, embedded labels, table is Purchase);

       

       

      Please suggest where I am making mistake.

      Any Help will be appreciated

        • Re: Section access :Access Denied
          Onno van Knotsenburg

          Please note that Qlik Sense does NOT use the password column, so I suggest you remove that.

           

          Without knowing the username of the person trying to login and the content of your Section Access.xlsx it is kinda hard to tell you why someone does not have access. My educated guess is "because the username is not in the section access table".

          • Re: Section access :Access Denied
            Chandrasheker Gompa

            Hi Shwetha,

             

            As per the above comment password need not be used in the qlik sense section access;

             

            and the thumb rule in qlik sense section access is to convert ACCESS, USERID, SALES CATEGORY into upper case before using it in the section access..

             

            and terminate section access with key word SECTION APPLICATION..

             

            I have a working section access logic which I have pasted below..

             

            I have used QVD's you can remove that and load directly from the excel as I have done in the first step

             

             

            tmp:
            LOAD
                "ACCESS",
                upper(opd) as USERID,
                UPPER("Cost Centre") AS [COST CENTRE]
            FROM [lib://QS_Cost_Center (opd_f29121)/Copy of Cost Centre Manager list_Test.xlsx]
            (ooxml, embedded labels, table is Sheet1);

            STORE tmp into [lib://QS_Cost_Center_QVD (opd_f29121)/temp_test_300.qvd];

            drop table tmp;

             


            tmp_sec:
            LOAD
                "ACCESS",
                "USERID",
                "COST CENTRE"
            FROM [lib://QS_Cost_Center_QVD (opd_f29121)/temp_test_300.qvd]
            (qvd);

            Concatenate
            LOAD * inline [
            ACCESS, USERID,COST CENTRE
            USER, OPD\F29121,*
            ];

             

             

            star is *;
            section access;
            Load * Resident tmp_sec;

            section Application;

            drop table tmp_sec;

              • Re: Section access :Access Denied
                shweta gupta

                Hi Chandrashekhar,

                 

                I am new to Qlik Sense, followed the Qlikview examples to implement this.

                 

                I am having few doubts :

                1. As you have mentioned "convert ACCESS, USERID, SALES CATEGORY into upper case before using it in the section access". Did you mean by the column headers or the content. As per my understanding it should be content of the corresponding field.

                - and 'ÚPPER' works in capitalising content under the fields (Correct ??) Which I have already done

                -but my field name all through the app is Sale.Category so please suggest should I make it in 'uppercase' everywhere


                2. Can you please elaborate how to terminate section access with key word SECTION APPLICATION


                So If I ll go by my code  following changes will help me to get rid off from the error



                SECTION ACCESS;

                LOAD

                    Upper([ACCESS]) as ACCESS,

                    Upper([USERID])as USERID,

                    Upper ([PASSWORD])as PASSWORD,  // Change 1. Remove Password

                    UPPER([CATEGORY]) as Sales.Category;  // Change 2: SALES.CATEGORY UPPERCASE

                LOAD

                    [ACCESS],

                    "USERID",

                    "PASSWORD",

                    CATEGORY

                FROM [lib://Folder/Section Access.xlsx]

                (ooxml, embedded labels, table is Section_Access);

                 

                 

                Section Application; //SECTION APPLICATION

                LOAD

                    Upper(Category) as Sales.Category,   // Change 3: SALES.CATEGORY in UPPERCASE

                    Product // In UPPERCASE

                FROM [lib://Folder/File.xlsx]

                (ooxml, embedded labels, table is Purchase);

                 

                 

                Any other suggested changes,

                Anyways I ll surely try above mentioned  code but it will be great help if you will help me in clearing above doubts

                 

                Thanks in Advance

                  • Re: Section access :Access Denied
                    Chandrasheker Gompa

                    ok I will clear your doubts...

                     

                    1. Convert the content and the headings used in the section access to upper case. Since you are loading the section application with sales category this will connect to your factual data so the other table which has the sales category also need to be in upper case.

                     

                    2. once the section application statement is encountered the section access logic gets completed... whatever you are loading below section application is just like any other table load.

                     

                    if you can provide sample data I will write the logic and test in a sample app and send it across... also please be sure of how you are populating the USERID .. mine is OPD\F29121    OPD is the domain name...

                     

                    Thanks

                      • Re: Section access :Access Denied
                        shweta gupta

                        Hi Chandrashekhar,

                         

                        I am now out of Access is denied problem. Thanks for suggestions.

                        Now I am having a new problem in the same case mentioned above :

                         

                        Whenever I am applying section access every user is getting reduced data but corresponding to all three mentioned category.

                        If I am not wrong with the logic , whatever I will add to the category my data will get reduced corresponding to CAT1, CAT2, CAT3  for USER1, USER2 and USER 3 respectively.

                         

                        i.e USER 2 and USER3 will not be able to see CAT1. (Right ?) but this is not happening, Following is my new code and the excel sheet format I am using.

                         

                        now my new script looks like this :

                         

                        SECTION ACCESS;

                        LOAD

                            ACCESS,

                            NTNAME,

                            UPPER([Category]) as [SALE.CATEGORY];

                         

                         

                        LOAD

                            ACCESS,

                            NTNAME,

                          Category

                         

                        FROM [lib://Haldirams (uic_shwetag)/Section Access.xlsx]

                        (ooxml, embedded labels, table is Section_Access);

                         

                         

                        Section Application;

                        LOAD

                            Upper(Category) as [SALE.CATEGORY]

                         

                        FROM [lib://Haldirams (uic_shwetag)/haldiram K R ENT - ZRP.xlsx]

                        (ooxml, embedded labels, table is Sale);

                         

                         

                        and my excel sheet for section access is this :

                        ACCESSNTNAMECategory
                        USERDOMAIN\USER1CAT1
                        USER
                        DOMAIN\USER2
                        CAT2
                        USER
                        DOMAIN\USER3
                        CAT3
                        ADMINDOMAIN\ADMINISTRATOR*

                         

                         

                        Need Suggestion again 

                         

                        I hope discussing this into the same thread is fine.

                  • Re: Section access :Access Denied
                    sujith madhavan

                    Make sure your userid is prefixed with userdirectory as well, this should be in your section access spreadsheet

                    so userid value should be like "SALES\USER001"

                    Also make sure you have admin user in the spreadsheet

                    values will be as below  ,

                     

                    ACCESS  = ADMIN

                    USERID = SALES\ADMINUSER

                    CATEGORY=*

                     

                     

                    SECTION ACCESS;

                    LOAD

                        Upper([ACCESS]) as ACCESS, // Took

                        Upper([USERID])as USERID,

                        UPPER([CATEGORY]) as CATEGORY;

                    LOAD

                        [ACCESS],

                        "USERID",

                        "PASSWORD",

                        CATEGORY

                    FROM [lib://Folder/Section Access.xlsx]

                    (ooxml, embedded labels, table is Section_Access);