2 Replies Latest reply: Jan 19, 2018 8:03 AM by Leigh Peiser RSS

    Qlik Sense:Can we create on demand report from oracle database (OR) QVD's?

    NAGA CHIRANJEEVI THOTA

      Hi All,

       

      We have a requirement for Qlik Sense report to have 7 years worth of data (Huge).

       

      But most of the time they will be using latest year data only.So they are fine to have 1 year worth of data in dashboard.

       

      But they are asking to have ability from Dashboard to load data of their choice (year)  dynamically from data base / QVD's  if required?

       

      Is this possible ? if yes, can you please explain how to implement it  in Qlik Sense?

       

       

      Thanks,

       

      Chiru

        • Re: Qlik Sense:Can we create on demand report from oracle database (OR) QVD's?
          Leigh Peiser

          Hi Naga

           

          You could achieve this by defining a variable in the App (Front End) for users to change (in Edit mode).
          This variable would then be used in the load script to determine what data to load.

           

          Example:

           

          // Create a variable called vYearsToLoad in the front end for users to edit using numeric values from 1 to 6

           

          LET vCurrentYear = Year(Today());

           

          if Len(Trim(vYearsToLoad)) = 0 then   // Check for current variable setting
          LET vMinYear = vCurrentYear-1;    // If no value set, then assume one year back only
          Else
          LET vMinYear = vCurrentYear-vYearsToLoad;  // User defined parameters
          EndIf


          TestTable1:
          Load * Inline [Years Loaded,
          2011,
          2012,
          2013,
          2014,
          2015,
          2016,
          2017,
          2018
          ]
          where [Years Loaded]>$(vMinYear);


          NOTE: This approach would only work for Apps in Workspaces, as users do not have the ability to set variables or initiate reloads in Published Apps (under Streams).
          This may not be ideal as Apps in Workspaces can only be accessed by one user at a time, unless they are unlocked by the Group Owner. Another risk with Apps in Workspaces is that they can also be edited by users in error.

           

          In order to get around the multi user issue for Apps in Workspaces, we created several versions of the same App so that if an App is locked, users could access the next available copy. However, if your data is huge then creating multiple versions could be problematic.

           

          Depending on the size of your data, a better option would be to just load all 7 years of data and allow users to select the years they would like to view through a Field list box on the landing page as they enter the App.
          Qlik Sense can handle pretty large datasets and this would allow your users to view what they want without having to set variables or initiate reloads.

           

          If the full 7 year dataset is too big for Qlik Sense Cloud, then you may have a problem with either of the above options anyway, so I'd definitely recommend loading the full dataset & testing the user experience with selections as the first option.

           

          Depending on how detailed you need your analysis to be, you could (as a last resort) load aggregated data instead of the full dataset.

           

          I hope this helps?

           

          Regards
          Leigh