10 Replies Latest reply: Dec 23, 2011 12:39 PM by Srini Srini RSS

    Dynamic Section Access on 2 Columns

      Hi Every1,

       

      I need your help.. :-)

      please find attached the excel input data file for section access.

       

      Prj_No

      Task_No

      Manager

      Executive

      Amt

      1101

      1

      SRINI

      MANISH

      100

      1101

      2

      SRINI

      MANISH

      110

      1101

      3

      SRINI

      SACHIN

      120

      1102

      1

      MANISH

      SRINI

      210

      1102

      2

      MANISH

      SRINI

      220

      1102

      3

      SACHIN

      RAHUL

      230


      i have already implemented section access on Column "EXECUTIVE" i.e, if i select MANISH then already my section access is working fine but i have another requirement.

      If i select any Executive then i need to search the same value in the MANAGER Column & show the relavant amounts because the executive can also be a Manager on the same or on different project codes.

      Section Access to be implemented on Manager and Executive (2 Columns)

       

      For example.,

      If NTUSER "MANISH" logs in then it should search for this value in MANAGER column and show me the output as below

       

      1102

      1

      MANISH

      SRINI

      210

      1102

      2

      MANISH

      SRINI

      220

       

      in the same way another NTUSER SRINI log in then his actual amount as a executive is coming fine but i also want to show the values as a manager & the output should be as below.

      1101

      1

      SRINI

      MANISH

      100

      1101

      2

      SRINI

      MANISH

      110

      1101

      3

      SRINI

      SACHIN

      120


      Need your help & suggestions for the same.. :-)

      Thanks in Advance..

       

      Rgds,

      srini

        • Re: Dynamic Section Access on 2 Columns

          Hi,

           

          Previously, I create something like this.

           

          In my scenario, I have Region and each Region has branches. When you logged in as a region manager, you see all the branches under that region.. when you logged in as a branch manager, you can only see the branch. I guess the logic is same..

           

          I attach the document.

           

          admin user name: admin

          password: admin1

           

          BR

           

          Omer

            • Re: Dynamic Section Access on 2 Columns

              Hey,

              Thanks a Ton for your Suggestion..

              I have gone through your Application but it was bit confusing.

               

              Can you help me with a sample application with the Excel File i have uploaded in my furst thread.

               

              Rgds,

              Srini

                • Dynamic Section Access on 2 Columns
                  Vijay Kumar

                  Hi Srini,

                  Do youn want 5 rows when login with Srini and 4 rows when login with Manish ????

                   

                  Vijay

                    • Re: Dynamic Section Access on 2 Columns

                      Hey Hi Vijay,... :-)

                       

                      Ok.. the Requirement is something like this.

                       

                      A executive can also be a manager in the same or different Projects, So when a executive loggs in he should be able to see a specific data (a specific Expression) which is now already working fine in my current Dashboard but there is another Expression which should not be based on the Executive selection rathar whomso ever is the Executive, QlikView should pickup the Value & pass it in the manager Field & the other Expression should get populated.

                       

                      For example,

                      in Case NTUSER MANISH Loggs in

                      First Expression should show the below output (Where my existing Dashboard is already working fine, As the Section Access is on Executive Name)

                      1101

                      1

                      SRINI

                      MANISH

                      100

                      1101

                      2

                      SRINI

                      MANISH

                      110

                      Total Amt 210

                       

                      But in 2nd Expression (It should be based on the manager name but not on the Executive name)

                      1102

                      1

                      MANISH

                      SRINI

                      210

                      1102

                      2

                      MANISH

                      SRINI

                      220

                      Total Amt 430

                       

                      In case of Rahul

                      First Expression should show me below output

                      1102

                      3

                      SACHIN

                      RAHUL

                      230

                      Total 230

                       

                      But for 2nd Expression there should not be any Rows, As he is not a Manager in any of the projects.

                       

                      Hope this clarifies your Doubt & to help me.. :-)

                       

                      Rgds,

                      srini

                        • Dynamic Section Access on 2 Columns
                          Sunil Chauhan

                          hello srini,

                           

                          Try below code.

                           

                          Section Access;

                          Load * inline [

                          Access,USERID,PASSWORD,MANAGER

                          Admin,Admin,Admin,*

                          User,User1,User1,SRINI

                          User,User2,User2,MANISH

                          ];

                           

                           

                          section application;

                          LOAD Prj_No,

                               Task_No,

                               Manager as MANAGER,

                               Executive,

                           

                               Amt

                          FROM

                          [C:\Documents and Settings\skchauhan\Desktop\sec access.xlsx]

                          (ooxml, embedded labels, table is Sheet1);

                           

                          Change the path for your Excel

                           

                           

                          Hope this help

                            • Re: Dynamic Section Access on 2 Columns
                              Sunil Chauhan

                              see the attached file

                               

                              user id -Admin

                              Password:Admin

                               

                               

                              hope this help

                                • Re: Dynamic Section Access on 2 Columns

                                  Hi Sunil,

                                   

                                  Thanks for your time spend..But Request you to please go thourgh the Last Post i have given above.

                                  My Question is if i have already implemented Section Access on Column "EXECUTIVE" for one of the Expressions then how come i can also implement the Section Access in the same Fact Table for another Expression.

                                   

                                  For Examples or scenarios please see my last post.

                                   

                                  Rgds,

                                  Srini

                                    • Re: Dynamic Section Access on 2 Columns

                                      Hi Srini,

                                       

                                      I understand your requirement, however, when you write it down MANISH, how system can understand whether s/he is Manager or Executive.. They are actually same person..

                                       

                                      Easiest way to overcome this problem, create a new column based on the project name and task name.. i guess, you show rows with respect to those two columns..

                                       

                                      In your section access table, define those fields..

                                       

                                      Hope that it will solve your problem..

                                       

                                      You can check it form the attached document..

                                       

                                      Admin user name: admin

                                      Admin password : admin1

                                       

                                      Manish user name: mn

                                      Manish password : mn1

                                       

                                      Srini user name: sr

                                      Srini password : sr1

                                       

                                      Rahul user name: rh

                                      Rahul password: rh1

                                       

                                      Section Access;

                                      LOAD * INLINE [

                                      ACCESS, USERID, PASSWORD, PROJECT

                                      admin,admin,admin1,*

                                       

                                       

                                      USER, sr, sr1, '11011'

                                      USER, sr, sr1, '11012'

                                      USER, sr, sr1, '11013'

                                      USER, sr, sr1, '11021'

                                      USER, sr, sr1, '11022'

                                      USER, mn, mn1, '11011'

                                      USER, mn, mn1, '11012'

                                      USER, mn, mn1, '11021'

                                      USER, mn, mn1, '11022'

                                      USER, rh, rh1, '11023'

                                      ];

                                       

                                       

                                      Section Application;

                                      LOAD Prj_No,

                                                 Prj_No&Task_No as PROJECT,

                                           Task_No,

                                           //Task_No as TASK,

                                           Manager as MANAGER,

                                           Executive as EXECUTIVE,

                                           Amt

                                      FROM

                                      [C:\Users\omerc\Downloads\Section Access.xlsx]

                                      (ooxml, embedded labels, table is Sheet1);

                                        • Re: Dynamic Section Access on 2 Columns

                                          Just ensure you get the correct result if the Exec and manager happen to be the same, ensuring the value doesn't get doubled. Andy

                                            • Re: Dynamic Section Access on 2 Columns

                                              Hi All,

                                               

                                              I would like to thank you all for your valuable Suggestions,

                                               

                                              I have got the Solution.

                                              First I have Loaded the resident of the Same Table Twice but in First Resident load only the Manager Name & in second Resident Loaded Table load only the Executive Name and concatenate both the tables.

                                              It will result in a common Name which can be used for comparison for any number of Expressions.

                                               

                                              ForExample:

                                              For First Expression i can Say if(ExecutiveName = CommonName,Amt)

                                              For Second Expression -- if(ManagerName=CommonName,Amt)

                                               

                                              The script will Look Like below---

                                               

                                               

                                              MainTable:

                                              Load

                                              Project_No&'-'&Task_No as CommonLink,

                                              Project_No,

                                              Task_No,

                                              Executive Name,

                                              ManagerName,

                                              Amt

                                              from ExcelFile.Xls;

                                               

                                               

                                              CommonTable:

                                              Load

                                              Project_No&'-'&Task_No as CommonLink,

                                              ExecutiveName as CommonName

                                              Resident MainTable;

                                               

                                              concatenate

                                               

                                              Temp:

                                              Load

                                              Project_No&'-'&Task_No as CommonLink,

                                              ManagerName as CommonName

                                              Resident MainTable;

                                               

                                              I am able to get a CommonName & using Set Analysis i am able to achieve my output.

                                              Thanks again for all your Suggestion but still i am looking at the Performance on a Hight Data Volumes.

                                               

                                                

                                              Rgds,

                                              Srini