Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi,
I am doing an incremental load where basically I am saving the newest data in a qvd and load the old data from another qvd. Then I just merge both qvds and remove any duplicate ones by looking at their IDs.
I am merging the qvds by doing this:
LET q_Books= 'BooksOld.qvd';
Books:
NoConcatenate
LOAD *
FROM BooksNew.qvd (qvd);
Concatenate
LOAD *
FRom BooksOld.qvd (qvd)
WHERE NOT(Exists(Ref_id));
STORE Books INTO '$(q_Books)';
Everything works good and the qvd is updated with the new data but as soon as the script finishes the 'script execution process' gets stuck and I have to force close the qvw.
Then I separated the storage of the new data in one qvw and the merging of the data in another qvw and everything worked fine and the 'script execution process' closed normally.
I really need both processes to be in the same qvw, does anyone know how to go about it please?
Thanks
HI,
Make sure you have a drop statement at the end of the script.
What could be the reason is, Qlikview is trying to create a sync table and due to huge filed list it hangs.
Regards,
Kaushik Solanki
Hi Kelly,
I suppose your user (the one doing the reload) does have RW access to that directory?
I have been doing similar things, building up historizations of several tables which do not have a history in the database.
What I generally do is a slightly different order of steps:
- First I load the data from the qvd that is already there
- Then I concatenate a LOAD from the live database (no need for an intermediary qvd), if necessary with a failsafe (in
case there are 0 records meeting all of my filters)
- Last, I store the whole thing away again.
In some cases (there can be restrictions, like, the app runs every 3hrs, but I want anything added to the historization_qvd only once a day), I need to slightly modify it:
- do the LOAD from the live database first (find out the nr. of rows for the failsafe if such is necessary)
- load the existing qvd
- concatenate a RESIDENT LOAD from the first (build in the failsafe here if necessary)
- store the whole thing away again.
HTH
Best regards,m
DataNibbler
Hi,
Thanks for you reply. Yes the user has the required acces to the directory.
Also, I have tried the Resident load, infact that was the approach I was working with at first, then I stored them in separate qvd for testing. The same happens when using 'Resident'; the 'script execution process' gets stuck and I have to force close qlikview.
The thing is that if I do them in separate qvws, everything works fine and also the qvds are updated succesfully.
Regards,
Kelly
Is correct that you declares a var like qBooks and then uses another like q_Books?
LET qBooks= 'BooksOld.qvd';
Books:
NoConcatenate
LOAD *
FROM BooksNew.qvd (qvd);
Concatenate
LOAD *
FRom BooksOld.qvd (qvd)
WHERE NOT(Exists(Ref_id));
STORE Books INTO '$(q_Books)';
Hi,
Sorry that was a typo, both variable names are the same in my load script.
Thanks
Hi
If you don't need the "Books" table in the qvw after storing it, try to drop it:
DROP Table Books;
That will make your qvw smaller on disk, and opening it will be quicker.
It may help your hanging issue, I don't know.
/gg
Hi,
Thanks for your reply. I tried that as well, still it does not work :S
Regards,
Kelly
HI,
Make sure you have a drop statement at the end of the script.
What could be the reason is, Qlikview is trying to create a sync table and due to huge filed list it hangs.
Regards,
Kaushik Solanki
Try saving it to another name.
SET qBooks= 'BooksOld.qvd';
Books:
LOAD *
FROM BooksNew.qvd (qvd);
Concatenate (Books)
LOAD *
FRom BooksOld.qvd (qvd)
WHERE NOT(Exists(Ref_id));
STORE Books INTO BooksTest;
Could be that QlikView is locking the Books.qvd for some unknown reason.
Hi Kelly,
if you have two or more tables with a lot of same field names qlikview will try to calculate the syntetic keys. Depending on the data volume this can take very long or even never end.
Solution: Make sure each Table has only one key field to every other table.
Hope this helps
Tobias