3 Replies Latest reply: Oct 18, 2011 4:11 AM by Miguel Angel Baeyens de Arce RSS

    MINUS Issue

      Hi,

       

      In some of the proposed reports that we are doing for our client, we are migrating reports from Business Objects.

       

      The Business Object reports implements dashboards based on SQL1 MINUS SQL2, similar to the UNION query.

       

      We are trying to implement the same report using QlikView.

       

      The issue we face is that SQL1 and SQL2 is based on parameters entered by the user. Hence it is not an option to do this while loading data into the QVD.

       

      Also since this involves a lot of data in the query it is not a feasible option to get the data run time from the data sources.

       

      I found the below solution, but I am not sure if this is too efficient to do inside QlikView, since it involves more data.

       

      http://community.qlik.com/message/61049

       

      Any guidance appreciated.

       

      Regards,

      Santosh

        • MINUS Issue
          Miguel Angel Baeyens de Arce

          Hi Santosh,

           

          Both the EXISTS() function or the MAPPING table will take longer than a usual load, since you need to load twice some of the data (at least the key field). Note that both solutions work only in the load script, that seems the fastest alternative, rather than load all the information and use any expression based on the data entered by the user to show or not to show the data.

           

          I'm sure you can get something similar with JOIN or KEEP, but my guess is that they will be less efficient.

           

          How long does your script to load and how many rows are you speaking of?

           

          Miguel Angel Baeyens

          BI Consultant

          Comex Grupo Ibérica

            • MINUS Issue

              Hi Miguel,

               

              The load takes around 10 - 11 mins to get the data from 8 tables (these tables are joined to get 2 tables in qlikview) with the volume of data is 30 million in one table and over 1 million in another. This data is only for couple of years. We need to futher extract data for 10 years.

               

              As the output from the MINUS part of the query is based on user input. Is there a way to pass user parameters to Exists() funtion in load script.

               

              Regards,

              Rajendra