Qlik Community

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
IMPORTANT security patches for GeoAnalytics Server available to download: READ DETAILS
cancel
Showing results for 
Search instead for 
Did you mean: 
gauthamchilled
Creator
Creator

section access more than one field

Hi,

I have gone thru HIC's blog on section access reduction in more than one field. Not sure how to build the following auth keys for all possible combinations.

how to get all possible combinations for the below like

   

COMPACOMPBCOMPCCOMPD
111ANY
11ANYANY

-                 -                -                   -

i know the script for this to concatnate and get the auth key for the bridge table. but how to generate the combinations easily..

not sure how many combinations it will have also. please help.

22 Replies
gauthamchilled
Creator
Creator
Author

Is it that complex to have section access for multiple fields?

luciancotea
Specialist
Specialist

Well, if you can combine your fields under a single hierarchy, you can specify a single node for them.

For example, if your 4 fields are:

CONTINENT - COUNTRY - REGION - PRODUCT CATEGORY

you can group first 3 fields into one hierarchy and then your security model would only have to specify

HIERARCHY NODE - PRODUCT CATEGORY

RedSky001
Creator III
Creator III

I ran into the same issue.  Once you get past 2 fields you need to start creating a lot of load statements and it's easy to screw it up.  so I wrote a script to create all the permutations, (for my dashboard I have 8 fields so 256 load statements are  required).


Anyway I know this post was some time ago but I've attached my modified script, hope you or someone finds this useful.

Mark


// Made up fact data

  FACT:

  load * Inline [ AuthIDTemp ,COMPA ,COMPB ,COMPC ,COMPD

  1 ,A ,B ,C ,D

  2 ,A ,B ,C ,D

  3 ,A ,B ,C ,D

  4 ,A ,B ,C ,D

  5 ,A ,B ,C ,D

  6 ,A ,B ,C ,D

  7 ,A ,B ,C ,D

  8 ,A ,B ,C ,D

  9 ,A ,B ,C ,D

  10 ,A ,B ,C ,D

  11 ,A ,B ,C ,D

  12 ,A ,B ,C ,D

  13 ,A ,B ,C ,D

  14 ,A ,B ,C ,D

  15 ,A ,B ,C ,D

  16 ,A ,B ,C ,D

  17 ,A ,B ,C ,D

  18 ,A ,B ,C ,D

  ];

  NoConcatenate

  Temp:

  load * Inline [ Field1

  X

  ALL

  ];

  NoConcatenate

  TblCombinaitons:

  load Field1 as Field1 Resident Temp;

  join load Field1 as Field2 Resident Temp;

  join load Field1 as Field3 Resident Temp;

  join load Field1 as Field4 Resident Temp;

  drop Table Temp;

  NoConcatenate

  SQL_TABLE:

  load

  if(Field1='X','COMPA',chr(39)&Field1&chr(39)) as Field1

  ,if(Field2='X','COMPB',chr(39)&Field2&chr(39)) as Field2

  ,if(Field3='X','COMPC',chr(39)&Field3&chr(39)) as Field3

  ,if(Field4='X','COMPD',chr(39)&Field4&chr(39)) as Field4

  Resident TblCombinaitons;

  drop Table TblCombinaitons;

  CountTable:

  load count(Field1) as FieldNameCount  Resident SQL_TABLE;

  let vRowCount = peek('FieldNameCount',0,'CountTable');

  drop Table CountTable;

  Temp:

  NoConcatenate

  load Distinct AuthIDTemp

  ,COMPA

  ,COMPB

  ,COMPC

  ,COMPD

  Resident FACT;

  AuthorizationBridgeTemp:

  Load distinct AuthIDTemp as %AuthIDTemp

  ,AuthIDTemp as AuthIDTemp

  resident Temp ;

  FOR i = 0 to vRowCount -1

  vFieldValue1 = peek('Field1',$(i),'SQL_TABLE');

  vFieldValue2 = peek('Field2',$(i),'SQL_TABLE');

  vFieldValue3 = peek('Field3',$(i),'SQL_TABLE');

  vFieldValue4 = peek('Field4',$(i),'SQL_TABLE');

    Concatenate(AuthorizationBridgeTemp)

    load distinct $(vFieldValue1) & '|' & $(vFieldValue2) & '|' & $(vFieldValue3) & '|' & $(vFieldValue4)  as %AuthIDTemp

    ,AuthIDTemp Resident Temp;

   

  Next i;

  drop Tables SQL_TABLE, Temp