topic Dynamic Section Access on 2 Columns in QlikView

Dynamic Section Access on 2 Columns

Mon, 19 Dec 2011 05:35:33 GMT

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

Mon, 19 Dec 2011 06:26:36 GMT

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

Omer

Re: Dynamic Section Access on 2 Columns

Mon, 19 Dec 2011 08:40:19 GMT

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_iitkgp — Mon, 19 Dec 2011 08:48:33 GMT

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

Mon, 19 Dec 2011 09:10:42 GMT

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

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

SunilChauhan — Mon, 19 Dec 2011 11:06:51 GMT

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

(ooxml, embedded labels, table is Sheet1);

Change the path for your Excel

Hope this help

Re: Dynamic Section Access on 2 Columns

SunilChauhan — Mon, 19 Dec 2011 11:10:28 GMT

see the attached file

user id -Admin

Password:Admin

hope this help

Re: Dynamic Section Access on 2 Columns

Mon, 19 Dec 2011 12:13:47 GMT

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

Tue, 20 Dec 2011 07:00:38 GMT

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

(ooxml, embedded labels, table is Sheet1);

Re: Dynamic Section Access on 2 Columns

Thu, 22 Dec 2011 20:48:07 GMT

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

Fri, 23 Dec 2011 17:39:53 GMT

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