partial reload

[#1]

Hi comunnity!

After I understood better the meaning og synthetic keys, now I´m trying to control all the fields I load. As I said before (other post) I don´t have to much tables but its size are significative, so any time I add or remove a field from my views I have to reload the script. Is necesary to do that or is there any option to reload just the "new" table without reloading all the rest? (of course, all the rest of data must be loaded)

Thank you, Fiber

[Miguel_Angel_Baeyens · Accepted solution]

Hello Fiber,

I usually use "Preceding load" to create and later modify manually a LOAD statement in the script, so I always control which fields are actually loaded regardless the tables in the datasource. For example

Table:LOAD Key, Field1, Field2, Field3;SQL SELECT Key, Field1, Field2, Field3FROM database.table1;

will load Field1, Field2 and Field3 fileds from the following select statement.

You can select * instead of any of the fieldnames. I don't know how can this affect performance. Besides, you can load from database, then store to a QVD file, and if needed, load from both database and existing QVD, but again you will have to check performance. I always store in QVD files becasue loading from these is extremely fast and saves time in development stages, and they can be used for several documents

Table:LOAD Field1, Field2, Field3FROM Table.qvd (qvd);INNER JOIN LOAD Key, Field4, Field5;SQL SELECT Key, Field4, Field5FROM database.table1; STORE Table INTO Table.qvd;

In this case, Field4 and Field5 are newer and added to the existing field.

As a last resource you can always do a SELECT * from your view, but anyway since you are creating new fields for existing records, these must be loaded into your document in one way or another, which will mean reloading from time to time.

Hope that helps.

[#3]

HI Miguel A.

Let's see if I understand that script code:

My initial situation is that I´ve got several tables like:

SQL SELECT *

FROM CUSTOMERS_TREASURY_REPORT;

SQL SELECT *

FROM SUPPLIERS_TREASURY_REPORT;

SQL SELECT ACCOUNT,
SMALL_TXT,
LARGE_TXT,
REPORT,
CLASIF_01,
CLASIF_02,
BANK,
TREASURY_AGRUPTATION;
FROM `ACCOUNTS`;

So, as I´m doing several modifications, to save time you suggest me I can divide ACCOUNT table in two parts. To do that, I must modify my script in those terms (¿?):

SQL SELECT *

FROM CUSTOMERS_TREASURY_REPORT;

SQL SELECT *

FROM SUPPLIERS_TREASURY_REPORT;

FIX_ACCOUNT:

LOAD ACCOUNT,
SMALL_TXT,
LARGE_TXT,
REPORT,
BANK,
TREASURY_AGRUPTATION;

SQL SELECT ACCOUNT,
CLASIF_01,
CLASIF_02,
FROM `ACCOUNTS`;

FIX_ACCOUNT:

LOAD ACCOUNT,
SMALL_TXT,
LARGE_TXT,
REPORT,
BANK,
TREASURY_AGRUPTATION FROM FIX_ACCOUNT.QVD;

INNER JOIN LOAD ACCOUNT,
CLASIF_01,
CLASIF_02;

SQL SELECT ACCOUN, CLASIF_01, CLASIF_02

FROM `ACCOUNTS`;

STORE FIX_ACCOUNT INTO FIX_ACCOUNT.QVD

Is it right? Sorry, but it´s too complicate for me.

I´m apprecite, Fiber.

[pover]

Fiber,

You can't call a column in a preceding load that you don't also call in your SQL statement. For example your first FIX_ACCOUNT table would cause an error on the reload because SMALL_TXT is not in the SQL statement. The LOAD statement reads information from you SQL statement. Also, it will be time consuming to load the whole ACCOUNTS table first in the same script.

Instead of the inner join, I would first focus on Miguel's later comments about the QVD files. Since you are still modifying the data model, I would first save entire tables into the QVD files in a separate QVW file commonly called a QVD Generator:

CUSTOMER_TREASURY_REPORT:
LOAD *;
SQL SELECT *
FROM CUSTOMER_TREASURY_REPORT;

STORE CUSTOMER_TREASURY_REPORT INTO CUSTOMER_TREASURY_REPORT.QVD;
Drop Table CUSTOMER_TREASURY_REPORT;

Then in a second QVW file where you make your data model, you just load the QVD with the columns you want and it should be alot faster.

LOAD ACCOUNT,
SMALL_TXT
FROM CUSTOMER_TREASURY_REPORT.QVD (QVD);

After you test the reload time of that, you might then focus on the inner joins to add columns.

Regards.

[#5]

Hi Karl,

this is espectacular!! It works. I do test in a very small qvw, but the script code is good. Thank you for your help. Tomorrow I'll prove it with the right one.

Really thank you!!