I strongly suggest using BUFFER LOAD instead of a simple load to speed up development cycle. At least for me one of the things that more consumes time is the script load.
Most of the times BUFFER LOAD works perfectly fine. However, there's a situation where it does not work as intended. It's like that :
FOR EACH ARQUIVO IN FILELIST C:\TEMP\*.QVD
DUMP:
BUFFER LOAD * FROM $(ARQUIVO) (QVD);
NEXT
Under this scenario, it looks like only the first file is loaded.
I have found a workaround here : simply loads the FOR/NEXT iteraction variable as a field. Then drop that field .
Like this :
FOR EACH ARQUIVO IN FILELIST C:\TEMP\LIXO\*.QVD
DUMP:
BUFFER LOAD '$(ARQUIVO)' AS DISCARD_DER_DUMP ,* FROM $(ARQUIVO) (QVD);
NEXT
DROP FIELD DISCARD_DER_DUMP;
At least for me using this workaround the BUFFER LOAD works fine.
Have you faced this problem as well ? Have you found a workaround ? Do you like to use BUFFER LOAD ?
The BUFFER LOAD wasn't working for us (4/19/2010) and QlikView Swede support told us it was because of a bug. Since then we stopped using BUFFER LOAD altogether. It sounds like it's working great for you though correct? Do you simply use BUFFER LOAD instead of LOAD in all scenarios?
"Buffer load" loads the table in the first time then stores it in a "generic" qvd file. In the next reload, if there are no changes in the scritpt, it loads from the qvd instead the original source. Since your source is a QVD, theres no gain in performance.
I rather use the "Store" functions and create my own qvd´s then loads from them to improve development speed.
As a matter of fact BUFFER is becoming a nightmare.
Yes, there must be a bug.
Sometimes QV uses the implicit buffer, even though the original file was modified.
I ended up doing a kind of wrapper , that is , a set of subroutines , that "CHECK" if BUFFER failed. If so, it aborts the script LOAD. Then I manually purge the buffers and run it again.
Cumbersome, I know. But at least I can use the BUFFERS many times during development.
The general idea of the "wrapper" that I build is to ALWAYS load a field into the file that is the timestamp of the original file. Then, after load is done, I compare the timestamp of the original file against this field. If they dont match, BUFFER failed, that is, BUFFER data was used, instead of the real data .
Here's how my original source code :
CALL BEGIN_LOAD('C:\TEST\QV\TEST065.CSV','TEST'); // SET UP FOR LOAD A FIELD WITH TIMESTAMP
TEST:
BUFFER LOAD $(FILE_TIMESTAMP) ,* FROM $(LOAD_PATH) (OEM, TXT, DELIMITER IS ';', EMBEDDED LABELS, NO QUOTES);
CALL END_LOAD; // HERE I COMPARE ORIGINAL FILE TIMESTAMP WITH LOADED FIELD
I attached TEST065.ZIP which is a self-contained QVW where you can see the idea.
ORIGI.ZIP is my original source code. I dont use internal editor altogether. All my scripts are "outside". Lots of facilities with my Text Editor (TSE from Semware). Grep and so much more than QV internal editor. That's why I always use $ INCLUDE.
As for the fact that BUFFER has a bug, AND QLIKTECH KNOWS ABOUT IT , there should be a WARNING at the handbook. Or this feature should be DISABLED (or fixed).
Cause it issues silent errors (mistakenly loaded data).
This is very serious and messes up the reliability of the analyses we do with Qv.
Anyway, thank you so much for your comment.
Hope you can benefit from my approach (or suggest something else) !
How're you doing ? We're close ! I live in Piracicaba.Guess you are from some QlikTech representative, isnt it ?
Anyway, here are some points :
Why load from a QVD ?
On our installation , a Unisys mainframe, we "dump" the mainframe tables that we want to load into QV onto a file server , in CSV file format. Right after the download process those CSVs are transformed into QVDs. This is done during the night and is totally automatic. We have built a tool for that. It's written in Cobol and is web based ! On this tool we simply specify what tables should be downloaded (and what fields). Also we may specify filtering rules....
So, IOW we have a kind of a "database" of QVD files on this file server.
Now, the real QVWs, that is, the scenarios, they will load these QVDs.
And YEEES, loading from QVD is pretty fast. We keep the CSV files above just because they are easy to "see". You can open them easily on a text editor.
Then , 99% of our final application scripts load data from those QVDs. Very, very few other data sources. One or another XLS.
Many times those final application LOADs are complex. They have formulas, derivated (calculated) fields, HASHs and stuff like that.
Then we use the implicit BUFFER that on it's turn will also create a underlying QVD !
I can ASSURE you that BUFFER is waaaaaaaaaaaaaaaaaaaaaaaaay faster that loading the original QVD, mostly when you have many calculated fields (or the table has many rows).
Anyway, you can make a test on your side.
On our case, we have scenarios that are loaded in minutes without buffer and in SECONDS when using buffers.
So, it's worth it. Totally.
Hope to have clarified the points !
Shoudl you have any doubt, let's talk in portuguese, OK ? Or you can call me. DDD dezenove telefone 3124 quinze noventa e cinco (não quis por o nro direto pra não aparecer no Google!). Obrigado !
O que quis dizer é que o buffer faz "automaticamente" a criação de um QVD e depois lê dele. Você pode fazer isso manualmente e a performance é a mesma.
Nos projetos aqui fazemos esse processo manualmente por controlar quais são os qvd´s, onde salvá-los e reaproveitá-los em outras aplicações.
Translating... the performance is the same if you create a qvd manually then load from it. We prefer this way because you can choose where the qvd´s are stored, their names and use them in other applications.
OK ! Vamos em Português então ! (sorry the foreing speakers - English later !)
Sim eu sei que posso fazer o BUFFER manualmente. Mas é meio chato ficar controlando isso ! E claro, "capcioso" (como traduzir capcioso??? rs).
Eu acho o BUFFER implicito do LOAD muito mais pratico. E o reaproveitamento ocorre naturalmente, entre aplicações inclusive. O QV "percebe" que o comando LOAD é o mesmo.
De qualquer forma, eu estou pelo menos detectando QUANDO o BUFFER implicito falha.
Grato !
----------------------------
Yes I know I can do the BUFFER manually. But its boring to control that. And, of course, catchy.
I think the implicit BUFFER of the LOAD way more practical. And, reuse happens naturally, amongs applications. QV notices that the LOAD statement is the same.
Anyway, at least I'm detecting WHEN the implicit BUFFER fails.
.png "fernandotoledo"){kind=avatar}
