topic Re: Section Access - union multiple fields in QlikView

Section Access - union multiple fields

Wed, 18 Jul 2012 18:58:09 GMT

I have an application where I need to set up Section Access to limit a user to seeing only the region that he is assigned, but also other regions that he may have opportunities to develop.

Which means I need to use multiple fields in the Section Access setup, but I need them to produce a UNION with each other, rather than the INTERSECTION.

Heres' my Section Access script:

Section Access;

LOAD * INLINE [

ACCESS, USERID, PASSWORD

ADMIN, ADMIN, ADMIN

USER, USER1, U1

];

Section Application;

star is *;

load * inline [

USERID,RegionReduction,TRegionReduction

ADMIN,*,*

USER1,Illinois,OH/Northeast

];

GrowerList:

LOAD GWR_ACCT_ID as [Account ID],

GWR_FIPS_CODE as [FIPS Code],

Proposed_2012_Region_Code as [Geo. Region Code],

Proposed_2012_Region_Name as [Geo. Region Name],

MKT_YR as [Market Year],

BRAND as Brand,

CROP as Crop,

if(BRAND='HUBNER','OH/Northeast',(if(BRAND='LEWIS','MO/KS/CO','Illinois'))) as [Transaction Region],

SumOfUNITS,

Proposed_2012_Region_Name as RegionReduction,

if(BRAND='HUBNER','OH/Northeast',(if(BRAND='LEWIS','MO/KS/CO','Illinois'))) as TRegionReduction

FROM

(ooxml, embedded labels);

When loaded, the above rsults in only one row, with 'RegionReduction' = 'Illinois' and TRegionReduction' = 'OH/Northeast' THis is as expected, but not what I need. What I need is for it to return all rows that have 'Illinois' in 'RegionReduction' as well as all rows that have 'OH/Northeast' in 'TRegionReduction'.

I've attached the QVW. Use 'ADMIN' as the ID & PW. 'USER1 & 'U1' is the regular userid/pw.

Sam

Re: Section Access - union multiple fields

Thu, 19 Jul 2012 11:35:32 GMT

try to use allways two rows for each user:

Section Application;

star is *;

load * inline [

USERID,RegionReduction,TRegionReduction

ADMIN,*,*

USER1,Illinois,*

USER1,*,OH/Northeast

];

Re: Section Access - union multiple fields

Thu, 19 Jul 2012 13:32:32 GMT

Thanks for the suggestion, but I've already tried that. It gives me no rows for USER1.

Re: Section Access - union multiple fields

Fri, 20 Jul 2012 07:46:53 GMT

how big are your user and Region/TRegion sets?

Re: Section Access - union multiple fields

Fri, 20 Jul 2012 13:37:40 GMT

Not very big. 20-30 distinct rows.

Sam

Re: Section Access - union multiple fields

Mon, 23 Jul 2012 11:06:40 GMT

looks like the only way it to make a key field on GrowerList

...

RegionReduction&'-'&TRegionReduction as Link

...

and then make a smart loop creating Link from all the listed values (both RegionReduction and TRegionReduction) on the Secction Access area

Re: Section Access - union multiple fields

Tue, 24 Jul 2012 14:57:50 GMT

I get the first part of your solution making as cobined field in GrowerList, but not sure what you mean by a 'smart loop'...could you explain?

Re: Section Access - union multiple fields

Tue, 24 Jul 2012 15:23:20 GMT

i mean if you dont want to paste "by hand" each permition (access) like:

user	dimension
user1	Illinois - OHWEST
user1	Illinois - Nevada
user1	Illinois - altalta
...	...

it it quite dumn and cuse a lot of mistakes

instead if that you can do smart loop reading your previous access table (as it looks at the begining)

i mean

- if it read RegionReduction- it create all the possible links with TRegionReduction

- if it read TRegionReduction it create all the links with RegionReduction

thats why i call it "smart loop" (i think it shouldn't be difficult but i call it "smart loop" 😜 )