MINUS Issue

[#1]

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

[Miguel_Angel_Baeyens]

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

[#3]

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

[Miguel_Angel_Baeyens]

Hi Rajendra,

There is no easy way to pass parameters to the load script. One way is create a QVW that only stores this input in a QVD file, that is later read by the QVW that creates the SQL accordingly. But it's a two file load anyway, one that asks the user for the values to build the SQL, the other to actually load from the database and do all the data model and so.

Hope that helps.

Miguel Angel Baeyens

BI Consultant

Comex Grupo Ibérica