QlikView-How to make an efficient program?

[Anonymous]

I have recently taken on a large project in which I am taking a history of Work Orders and subtracting a history of Invoices in order to estimate a history of inventory. In order to do this I am using a JD Edwards database and pulling data from there. As I am learning as I go, however, the process has been slow. As of right now I right a couple lines of script and then 'debug' with a limited value of usually 100 lines to see if it works. If it works I then repeat the debug but increase the line count to about 250K lines. This worked for a while, but as I add each new table, formula or what have you, it takes a longer time to complete the test.

So I have several questions:

1. Is there a faster way to test if the script is correct without having to run an acctual 'debug'?

2. Is there a way of having the program run off of a set table rather than having to go thru the database everytime, as I believe this might be slowing things down?

3. What could be some common mistakes that amatuer QlikView users make that result in highly inefficient programs?

Thanks.

[Lee_Matthews]

I would recommend writing your script to get the data from the database, but then as you add each table, add a line to dump the data out to QVD. Then, with the next table you add, change the previous table to get the data from QVD and comment out the database load. Loading from QVD is very fast. Just use

STORE tableName INTO filename.qvd (qvd);

to store to QVD. And then create your load from QVD by using the normal load from file source button.

In terms of efficient design, a simple rule is to try and place all of the calculations in your load script rather than in your UI. Try for a star schema and avoid having joins between many small tables in QlikView - its an associative database, not a relational database, so normalisation of the data model is not important. And dont use composite keys in QlikView as they result in synthetic key tables (which are not efficient with large data sets).

[#3]

Though I haven't answered you for everything. I recommend to use partial reload whenever you are adding any new tables into the application. Partial reload, loads only the new table into the application instead of loading all the tables again in to the application. Doing this you don't need to go through the database everytime other than the newly added table.

[Anonymous]

Lee Matthews- So after each table I create or reference in the script I should just include the "STORE tableName INTO filename.qvd (qvd);" at the end of the section? Thank you very much!

However, I don't really understand how to "change the previous table to get the data from QVD and comment out the database load" or how to "create your load from QVD by using the normal load from file source button". I understand the idea I'm trying to achieve, but as I'm very new to QlikView, I have no idea of how to do these things logistically.

Thanks again for the response, I think I'll be able to get started.

At titude- That will work as far as speeding up my load time and then I wont be wasting time just sitting in front of my computer. So thank you!

[#5]

Lee Matthews- So after each table I create or reference in the script I should just include the "STORE tableName INTO filename.qvd (qvd);" at the end of the section? Thank you very much!

However, I don't really understand how to "change the previous table to get the data from QVD and comment out the database load" or how to "create your load from QVD by using the normal load from file source button". I understand the idea I'm trying to achieve, but as I'm very new to QlikView, I have no idea of how to do these things logistically.

After loading the data from datasource you need to store it in a QVD file as follows.

tableName:

Load field1,field2 from table.xls;

STORE tableName INTO filename.qvd (qvd);

drop table tableName; // As once the above QVD is created

Above thing you can do it in a seperate qvw(This qvw will be used only for create the qvd).

After that in your real qvw which contains the UI you can load the data from above qvd as follows:

Edit Script --> Table files --> Select the filename.qvd --> finish --> Reload.(This step will help you to load the data from qvd)