Advanced Section Access (Data Reduction) based on a value in any of 4 fields

[#1]

Initial Set up: Suppose I have 4 Fields: F1,F2,F3 and F4 which can contain a finite number of values: A,B,C...etc.

Main Question:   How to use section access/dynamic data reduction to allow a particular user to see any rows where a particular value appears in any of the 4 fields above.  For example:  Reduce data for a particular user to any rows where F1 = A OR F2 = A OR F3 = A or F4 = A...

What I have already tried:

1) My first thought was to try and create a composite field to be used for reduction: F1&'-'F2&'-'&F3&'-'&F4 AS REDUCTION_FIELD and somehow be able to "search" for a particular reducing value in the reduction field (ie *A*).  However, this does not work because the * ' s in this case are read as literal strings values as opposed to wildcards in the section access file.

2) I have already successfully implemented the complex authorization in this post (Basics for complex authorization) on 3 other fields and thought of just adding the 4 other fields to the mix.  However, I ruled this out because my authorization bridge table would require 2^7 = 128 loads and it would continue to double with each additional field I would need to add to section access in the future.

Right now, I'm thinking of using option #2 but write a UDF to automatically generate the authorization bridge table based on a set of reduction field inputs.

Any other ideas?

Thanks,

Andrew

[Kushal_Chawda]

try creating the Flag like if( F1=F2=F3=F4, 1,0) as Flag in script

Now use this Flag in Saction access table

USER, PASSWORD, Flag

User1, User1, 1

.

,

,

[#3]

Thanks for the reply.

However, I do not think this would work because all fields 1 - 4 could have a different value.  For example, see some possible sample rows of data below... in all cases user with access to value A should see all rows (because the value A appears in one of the 4 fields)

F1	F2	F3	F4
A	B	C	D
B	C	A	D
B	<Blank>	D	A

[rwunderlich · Accepted solution]

I think you could create a new table of all values for each row (Key) like this:

Combo:

LOAD

  Key,

  subfield(F1 & '|' & F2 & '|' &F3 & '|' &F4, '|') as AllValues

RESIDENT Transactions

;

And then use the AllValues field as your reduction field.

-Rob

http://masterssummit.com

http://qlikviewcookbook.com

[#5]

Rob,

Thanks for the idea.  This helped me solve the problem.

I ended up creating a separate table (using a row ID key to link back to the facts) with all of the reduction fields needed (ie the "AllValues" column you mentioned above as well as the 3 other fields I was already using for reduction) and then applied the concepts in Henrics post (Basics for complex authorization) to create the Authorization bridge table off of this new table.

Thanks!

Andrew