Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Where Exists on loading from QVD ?

Hi

[This follows on from thread Qlik Sense takes almost three times longer to reload than QlikView but I am creating a new thread as this question is purely regarding Where Exists on loading from QVD]

I have created a test QVD using :

Transactions:

Load

TransLineID,

TransID,

mod(TransID,26)+1 as Num,

Pick(Ceil(3*Rand1),'A','B','C') as Dim1,

Pick(Ceil(6*Rand1),'a','b','c','d','e','f') as Dim2,

Pick(Ceil(3*Rand()),'X','Y','Z') as Dim3,

Round(1000*Rand()*Rand()*Rand1) as Expression1,

Round(  10*Rand()*Rand()*Rand1) as Expression2,

Round(Rand()*Rand1,0.00001) as Expression3;

Load

Rand() as Rand1,

IterNo() as TransLineID,

RecNo() as TransID

Autogenerate 10000000

While Rand()<=0.5 or IterNo()=1;

store Transactions into TransactionQV112.qvd (qvd) ;

Then loaded it with a Where Exists as per below :

Temp:

LOAD * INLINE [

    Expression1

    1

];

Transactions:

load * from  TransactionQV112.qvd (qvd)

Where Exists (Expression1)

;

It seems as if in QlikSense the Where Exists is ignored and the whole QVD is loaded.

Whereas in QlikView the Where Exist is used and only the required rows are loaded.

Any ideas anybody ?

Best Regards,     Bill

31 Replies
Employee
Employee

Re: Where Exists on loading from QVD ?

Hi Bill - let me take a look.

Sean Donovan - do you have any thoughts on this?

Mike

Not applicable

Re: Where Exists on loading from QVD ?

Michael Tarallo Sean Donovan


I found this is [only] the case when the QVD is created via Sense. If the QVD is created via QlikView the where exists function works properly. The log file calls out the origination file source too so there may be something unique in the QVD creation between the two programs:

Log using Sense QVD:
Transactions << TransactionQV112
(QVD columnar optimized)

Log using QlikView QVD:
Transactions << TransactionQV112
(old QVD (row based) optimized)
QVD file generated by version 11.2 or lower was detected. Use STORE INTO to optimize and columnize this file.

Employee
Employee

Re: Where Exists on loading from QVD ?

Hi Guys - I reported this to R&D

Mike

Re: Where Exists on loading from QVD ?

Mike

Did you manage to reproduce the issue ?

Just want to check whether or not it is my paranoid imagination.

Best Regards,     Bill

Employee
Employee

Re: Where Exists on loading from QVD ?

Hi Bill - Yes, Sean D was able to reproduce it. I am told that this should not happen, and I logged it with R&D.

Mike

Re: Where Exists on loading from QVD ?

Thanks Mike.

I'll await your response to this thread when your R&D get back to you.

Regards,     Bill

josecardenas
New Contributor III

Re: Where Exists on loading from QVD ?

Hi Bill and Michael

Any news with this issue?

Sorry for the straight question!, but I'm working in a demo for my company and despite I've got good results making the full load, when I've just started to code some more professional solution I'm getting the same issue in this post.


The code for incremental is quite simple:

FullTarget:

LOAD * FROM (QVD)

NewSet:

NoConcatenate

LOAD .... SELECT;

CONCATENATE (FullTarget)

LOAD pkid, ....

RESIDENT NewSet

WHERE NOT EXISTS(pkid)

DROP TABLE NewSet;

This feature is a precondition in order to code the right way to load the data in future production environments.

Thank you for your comprehension and answer!

-Jose

Re: Where Exists on loading from QVD ?

Maybe Michael Tarallo will be able to advise,

Employee
Employee

Re: Where Exists on loading from QVD ?

Hi Guys - let me see what I can do.

Mike