topic force store to execute in QlikView

force store to execute

Mon, 30 Aug 2010 22:26:26 GMT

Hi all,

I'm trying to write a single QVD builder which will create my prod QVD from a sql statement and then create a "dev" qvd in a different path from a load from the previously created QVD.

Apparently, the STORE is not executed until the entire script finishes because I'm getting a "table cannot be found" error. After passing the error, the file is created and the second half can run successfully.

Is there a command to force the store to generate the file ? Something like.......

SET _QVDDEVPath='\\dev_server\data';

SET _QVDProdPath='\\prod_server\data';

SET _QVDName='table_name';

// create full prod qvd and store into prod path

$(_QVDName):

select col1, col2 from my_database_table;

STORE $(_QVDName) INTO $(_QVDProdPath)$(_QVDName).QVD (QVD);

///////////////////////////////////////

FORCE STORAGE TO EXECUTE HERE

///////////////////////////////////////

// create "dev" qvd with same name in dev path with data limited.

LOAD * from $(_QVDProdPath)$(_QVDName).QVD (QVD) where date_column > Today()-60;

STORE $(_QVDName) INTO $(_QVDDevPath)$(_QVDName).QVD (QVD);

force store to execute

Mon, 30 Aug 2010 22:46:27 GMT

I've never expereinced the stores being "batched up" to the end of the script, my stores always worked sequentially and i experience pauses in script as the file is being written before it loads the next table and stores it and i have one or two files which do the same as yours (load from a qvd generated in the same script) but it's not the most effeicent way of doing it .

In your case, i would simply change the

LOAD * from $(_QVDProdPath)$(_QVDName).QVD (QVD)

Devtable:

LOAD * resident $(_QVDName) where date_column > Today()-60;

Also if your qvw is purely to there to build qvd's then it ususally a good idea to have a "drop table Devtable;" after the succesful storing to free up memory and decrease the qvw file size, it can also help with debugging as you only have one table to deal with at a time.

force store to execute

Oleg_Troyansky — Mon, 30 Aug 2010 23:22:46 GMT

I think your problem doesn't have anything to do with the STORE command... what's happening (as far as I can see...) is automatic concatenation. Your "dev" table has exactly the same set of fields as the "prod" table, and the two tables are getting concatenated automatically.

To avoid concatenation, add prefix NOCONCATENATE before the second LOAD.

Another possible problem could be caused by the QVD file being still open when you are trying to read from it. Two thoughts with this regard:

1. You should still have the same table "resident" in memory, - you can simply generate your "DEV" table using the table in memory and not read from QVD.

2. To force the QVD file to get closed, try either reading or writing another "dummy" Qvd file - one of the two should force the previous file to get closed.

force store to execute

Tue, 31 Aug 2010 01:21:53 GMT

thanks guys. i ended up using noconcatenate and loading from resident table. very nice.

thanks for the help.