Master Calendar and Section Access

[sfbi]

Hi,

I'm having an issue with master calendar and session access on Qlik Sense (Saas).

My Session Access Script, use the field [ID_LOCATION] as REDUCTION to reduce data for each user, but the same [IDLOCATION] is linked to the fact table and Date Bridge:

Section Access;
LOAD
ACCESS,
USERID,
§REDUCTION,
OMIT
FROM tbX;

Section Application;

....

FACT_TABLE:
LOAD
[ID_FACT],
[DATE_FACT],
[ID_LOCATION],
[QT_FACT]
FROM tbFACT;

DateBridge:
Load 
[ID_FACT], 
[DATE_FACT] as CalendarDate, 
'FACT' as DateType
Resident FACT_TABLE;

The MasterCalendar script follow as usual after this part.

If I "turn off" the session access script, all date range are set correctly. 

My question is: there's any way to ignore the session access (IDLOCATION/REDUCTION) when creating the MasterCalendar? Or any other idea? I can't exclude the [ID_LOCATION] from the fact table, as I need it as a dimension on the app.

Thank you,

Henrique

[sfbi · Accepted solution]

I got a solution for this specific case. I've created a second REDUCTION field (§REDUCTIONDATE), and gave it the 'ALL' value. 

here is the script. Any improvement please feel free to colaborate.

//LOAD THE LOCATION TABLE AND RENAME THE FIELD TO REDUCTION.

LIB CONNECT TO 'LibX:LOCATION';
LOCATIONSession:
SQL SELECT 
"IDLOCATION"
FROM JSON (wrap off) "LOCATION";

LOCATION:
LOAD	IDLOCATION AS REDUCTION
RESIDENT LOCATIONSession;
DROP TABLE LOCATIONSession;

//CREATE THE §REDUCTION TABLE
§REDUCTION:
Load distinct
REDUCTION,
REDUCTION as §REDUCTION
resident LOCATION;
//DROP LOCATION TABLE.
drop table LOCATION;

//APPLY ALL A §REDUCTION
Load
REDUCTION,
'ALL' as §REDUCTION
resident §REDUCTION;

//CREATE THE SECTION ACCESS. TWO REDUCTION FIELDS, FOR DIFFERENT PURPOSES. 
//ALL USERS HAVE THE 'ALL' VALUE SET FOR REDUCTIONDATE FIELD ON THE AUTH TABLE.
Section Access;
LOAD
    ACCESS,
    USERID,
    §REDUCTION,
    §REDUCTIONDATE,
    OMIT
FROM [YOUR TABLE OR FILE];

//CREATE THE SECTION APP.
Section Application;

//NOW LOAD THE FACT TABLE. ADDED THE 'ALL' VALUE AS §REDUCTIONDATE. I NEED TO RENAME DAFACT TO TempDate TO AVOID CIRULAR REFERENCE.
//USING INCREMENTAL LOAD FRO QVDs. SO THERE'S NO SQLs ON THIS LOADS.
FACT_TABLE:
LOAD
    IDFACT,
    DATE(DAFACT,'DD/MM/YYYY') as TempDate,
    IDPLACE,
    FACT_QTD,
    'ALL' AS §REDUCTIONDATE   
FROM FACT_TABLE;

//LOAD THE PLACE TABLE. IN THIS CASE IS AN INTERMEDIATE "LOCATION" THAT IS LINK THE FACT TABLE TO THE LOCATION TABLE.
PLACE:
LOAD
    IDPLACE,
    NMPLACE,
    IDLOCATION
FROM PLACE_TABLE
where Exists(IDPLACE);

//RELOAD THE LOCATION AND CREATE THE REDUCTION LINK. 
LOCATION:
LOAD
     IDLOCATION,
     IDLOCATION AS REDUCTION,
     NMLOCATION
    where Exists(IDLOCATION);
SQL SELECT 
	"IDLOCATION",
	"NMLOCATION"
FROM LOCATION_TABLE;

//NOW CREATE THE "MASTER CALENDAR".

//IN THIS CASE, I'M USING TODAY() AS THE MAX DATE.  
Temp:  
Load  
min(TempDate) as minDate,
TODAY() as maxDate  
Resident FACT_TABLE;  
      
Let varMinDate = Num(Peek('minDate', 0, 'Temp'));  
Let varMaxDate = Num(Peek('maxDate', 0, 'Temp'));  
DROP Table Temp;  
      
TempCalendar:  
LOAD  
$(varMinDate) + Iterno()-1 As Num,  
Date($(varMinDate) + IterNo() - 1) as TempDate  
AutoGenerate 1 While $(varMinDate) + IterNo() -1 <= $(varMaxDate);  

//AGAIN SET 'ALL' TO §REDUCTIONDATE SO DATES WON'T BE REDUCED BY THE SECTION ACCESS.
MasterCalendar:
Load  
  DATE(TempDate,'DD/MM/YYYY') AS TempDate,
  'ALL' AS §REDUCTIONDATE,
  week(TempDate) As Week,  
   Year(TempDate) As Year,  
   Month(TempDate) As Month,  
   Day(TempDate) As Day 
Resident TempCalendar  
Order By TempDate ASC;
Drop Table TempCalendar;

//CREATE AN OUTER JOIN TO ALL DATES HAVE A LINK TO THE PLACES (IDPLACE), SO SELECTIONS BY PLACE WILL SHOW ALL DATES ALSO, EVEN IF PLACE DONT HAVE RELATION TO THE FACT DATE. THIS IS USEFUL FOR VISUALIZATIONS.
Outer Join
Load Distinct
IDPLACE
Resident PLACE;

//RELOAD THE FACT TABLE.
ReloadFact:
NoConcatenate
LOAD Distinct
IDFACT,
IDPLACE,
TempDate,
FACT_QTD
Resident FACT_TABLE;

//DROP TNE FACT_TABLE
Drop Table FACT_TABLE;

[Brett_Bleess]

So what I think you were hitting was related to the 'strict exclusion' setting, which is not adjustable in Sense, but it is in QlikView, but basically, if things occur such that a user does not have a any values attached to their view for the reduction field, that would lock them out of the application, so that is the only explanation I have at this point is something was causing a situation where there was no reduction value for some users etc., and therefore they were not allowed to open the application once Section Access with the reduction was applied.  The other thing you could have done in this case is just created the ALL value in the other reduction field that had a mapping table to map that value to all the other possible values, that is the only way around this of which I know, hopefully makes a little sense.  I would say what you did should be fine too though.  

May want to review the following Help link, that may cause something to click too:

https://help.qlik.com/en-US/sense/June2020/Subsystems/Hub/Content/Sense_Hub/Scripting/Security/manag...

Regards,
Brett