Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
Hi Bill - let me take a look.
Sean Donovan - do you have any thoughts on this?
Mike
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.
Hi Guys - I reported this to R&D
Mike
Mike
Did you manage to reproduce the issue ?
Just want to check whether or not it is my paranoid imagination.
Best Regards, Bill
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
Thanks Mike.
I'll await your response to this thread when your R&D get back to you.
Regards, Bill
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
Maybe Michael Tarallo will be able to advise,
Hi Guys - let me see what I can do.
Mike