Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
sfbi
Creator
Creator

Master Calendar and Section Access

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

Labels (2)
1 Solution

Accepted Solutions
sfbi
Creator
Creator
Author

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;

 

View solution in original post

11 Replies
Brett_Bleess
Former Employee
Former Employee

I would have a look at the two following Design Blog posts, hopefully they will provide the information you need to get things working as you wish:

https://community.qlik.com/t5/Qlik-Design-Blog/A-Primer-on-Section-Access/ba-p/1465766

https://community.qlik.com/t5/Qlik-Design-Blog/The-Master-Calendar/ba-p/1471527

Sorry I do not have anything better, but hopefully this helps you move forward.

Regards,
Brett

To help users find verified answers, please do not forget to use the "Accept as Solution" button on any post(s) that helped you resolve your problem or question.
I now work a compressed schedule, Tuesday, Wednesday and Thursday, so those will be the days I will reply to any follow-up posts.
sfbi
Creator
Creator
Author

Thank you Brett... Unfortunately I've checked both links already and couldn't find a way yet...

Brett_Bleess
Former Employee
Former Employee

Try moving your Section Access script to the end of the script to see if that does the trick, only potentially easy thing of which I can think that might help given what you said about things.

Regards,
Brett

To help users find verified answers, please do not forget to use the "Accept as Solution" button on any post(s) that helped you resolve your problem or question.
I now work a compressed schedule, Tuesday, Wednesday and Thursday, so those will be the days I will reply to any follow-up posts.
rubenmarin

Hi, maybe you can add a not-real FACT that is assigned to all dates and locations.

tmpSynthFact:
LOAD 
  FieldValue('DAFACT', RecNo()) as DAFACT
Autogenerate FieldValueCount('DAFACT');

Outer Join (tmpSynthFact)
LOAD 
  FieldValue('ID_LOCATION', RecNo()) as ID_LOCATION,
  0 as ID_FACT
Autogenerate FieldValueCount('ID_LOCATION');
Concatenate (FACT_TABLE) LOAD * Resident tmpSynthFact;
DROP Table tmpSynthFact;

 The counterpart is that this fact will be shown, this can be avoided using one field to link tables and another to show the data on the app.

sfbi
Creator
Creator
Author

Hi @rubenmarin 

I got your point. Tried that, but anytime I need to link it to the Fact Table, by any field, the section access won't show all dates (that's the way it works right?)...

Using different fieldname is possible, but I'll lose all links with other tables and show data that is not allowed for some users... I've changed on fact table [ID_LOCATION] to [ID_LOCATION_X] and loaded the LOCATION TABLE with the same changes ID_LOCATION_X, NM_LOCATION_X ... got all dates on mastercalendar, but as section access is not reducing data now, it is showing all LOCATIONs to all users now, witch is not allowed...

This situation might not have a solution, unless section access could ignore some fields to be reduced...
Anyway, thank you!

sfbi
Creator
Creator
Author

@Brett_Bleess  this action disable the section access

Brett_Bleess
Former Employee
Former Employee

Moving Section Access table load to the end of the script should not impact things, it should still work, be sure you exit the application after reload though and reopen, as post reload, the app will be wide open, that is by design, you have to save, close and reopen for Section Access to kick back in...  

Regards,
Brett

To help users find verified answers, please do not forget to use the "Accept as Solution" button on any post(s) that helped you resolve your problem or question.
I now work a compressed schedule, Tuesday, Wednesday and Thursday, so those will be the days I will reply to any follow-up posts.
sfbi
Creator
Creator
Author

Hi Brett,

You are right... Last time I moved the precede loads also to the end and that "turned off" the SA . Now I moved just the Section Access part and got it working as expected. Unfortunately it did not solved the problem.

On QS SaaS  I don't need to close and reopen the app to the Section Access work. Double tested it. Just by reloading the app the data is reduced as expected or shown if I "turn off" the script.

Brett_Bleess
Former Employee
Former Employee

Ah, sorry, forgot you were in Sense, I get stuck in QlikView and that is how things work there, apologies for the confusion.  Not sure regarding the underlying issue here, if this can be replicated pretty easily, I would suggest opening up a support case, so we can have a look at things more directly at this point.  I know there are a few quirks about Section Access in Sense, but off the top of my head, I cannot think of what may be wrong.  The only other thing I could recommend is looking at the option of commenting you Section Access line in the script to have that table load as part of your visible data model such that you can see if things are linking correctly that way and you get the expected reductions when selecting different users, only way of which I can think to try to narrow things down on your own.  That should work in Sense like it does in QlikView as far as I know, and that may provide a clue.

Regards,
Brett

To help users find verified answers, please do not forget to use the "Accept as Solution" button on any post(s) that helped you resolve your problem or question.
I now work a compressed schedule, Tuesday, Wednesday and Thursday, so those will be the days I will reply to any follow-up posts.