topic Re: Multiple Exists() functions, but still keep optimized load in QlikView

Multiple Exists() functions, but still keep optimized load

Wed, 14 Nov 2012 12:39:47 GMT

If my script contains one Exists funtion, the Optimized load works just fine eg

where Exists(Cost_Ctr_Cde)

if I add an additional Exists function, the load is no longer optimized?

where Exists(Cost_Ctr_Cde)
and Exists(Period)

any suggestions on how to keep the load optimized?

Thanks

Re: Multiple Exists() functions, but still keep optimized load

Wed, 14 Nov 2012 12:57:59 GMT

where exists(Cost_Ctr_Cde,Period) try this

Re: Multiple Exists() functions, but still keep optimized load

Wed, 14 Nov 2012 13:03:07 GMT

I tied this, but the load is no longer optimized, so really slow...

Re: Multiple Exists() functions, but still keep optimized load

Wed, 14 Nov 2012 13:34:54 GMT

Hi,

I think only the usage of 'as','alias' keywords (and in your case, 'Exists') does not affect optimized load.
Perhap, since you are using Exists in conjunction with 'and', QlikView isn't treating it as optimized.

You could use Exists twice on the same table (say, Table1) but store this table into a QVD. Drop the table and read the QVD instead. If you are implementing a layered structure, you can even implement this procedure at an earlier stage and read the QVD in this stage.

Hope that helps.

-Khaled

Re: Multiple Exists() functions, but still keep optimized load

jonathandienst — Wed, 14 Nov 2012 13:36:40 GMT

You could create a field which is the concatenation of these two field, and do the exists on that. The concatenated field must exist in the qvd so it must be created when the qvd is built. You could drop the concatenated field at the end of the load if it is not required in the final model.

Regards

Jonathan

Re: Multiple Exists() functions, but still keep optimized load

Thu, 15 Nov 2012 04:26:14 GMT

Thanks for the suggestions, its a pity that one cant use multiple Exists() functions

Re: Multiple Exists() functions, but still keep optimized load

Anonymous — Sat, 17 Nov 2012 23:19:12 GMT

Hi Andrew,

you could do an inner join after the exits in order to keep optimized load:

Table1:

...

where exists(Cost_Ctr_Cde);

inner join(Table1)

load * inline [

Period

your period(s)

];

hth, regards

Re: Multiple Exists() functions, but still keep optimized load

Sun, 18 Nov 2012 14:30:06 GMT

Thanks for the suggestion. The problem is that Table1 would load all Periods, which is not ideal as we have large amount of data spanning a number of years.

I know there are other ways of doing it, but was determined to find a solution using multiple Exists() functions