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.
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;
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:
Sorry I do not have anything better, but hopefully this helps you move forward.
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.
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.
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!
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...
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.
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.