topic Re: Master Calendar and Section Access in App Development

Master Calendar and Section Access

sfbi — Sat, 16 Nov 2024 01:53:17 GMT

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

Re: Master Calendar and Section Access

Brett_Bleess — Thu, 16 Jul 2020 12:04:39 GMT

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

Re: Master Calendar and Section Access

sfbi — Thu, 16 Jul 2020 12:48:13 GMT

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

Re: Master Calendar and Section Access

Brett_Bleess — Thu, 16 Jul 2020 17:05:53 GMT

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

Re: Master Calendar and Section Access

rubenmarin — Fri, 17 Jul 2020 06:27:19 GMT

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.

Re: Master Calendar and Section Access

sfbi — Fri, 17 Jul 2020 14:52:52 GMT

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!

Re: Master Calendar and Section Access

sfbi — Fri, 17 Jul 2020 16:44:58 GMT

@Brett_Bleess this action disable the section access

Re: Master Calendar and Section Access

Brett_Bleess — Wed, 22 Jul 2020 14:05:33 GMT

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

Re: Master Calendar and Section Access

sfbi — Thu, 23 Jul 2020 12:09:23 GMT

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.

Re: Master Calendar and Section Access

Brett_Bleess — Thu, 23 Jul 2020 12:16:23 GMT

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

Re: Master Calendar and Section Access

sfbi — Thu, 23 Jul 2020 12:57:37 GMT

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;

Re: Master Calendar and Section Access

Brett_Bleess — Thu, 23 Jul 2020 13:09:44 GMT

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/manage-security-with-section-access.htm

Regards,
Brett