Write data to qvd file and reload only new info

[razvan_brais]

Hy there ,

I`m trying to upload some info from a database wich is kinda big. So i have created a qvd file wich has the old information , and concatenate it with the new info. But every time i run the script it doubles the record values in my application, the qvd file size remains the same. What I`m doing wrong.

The script looks something like this:

LET lastReloadTime = ReloadTime();

IF QvdCreateTime('pathToQVD FILE') then

MyTable:

LOAD "DATE",

    "COL1",

    "COL2",

FROM    [pathToQVD FILE] (qvd);

ELSE

MyTable:

LOAD "DATE",

"COL1",

    "COL2";

SQL SELECT *

FROM "MY_TABLE" where trunc(DATE)>=to_date('20160101','YYYYMMDD') and trunc(DATE)<=to_date('20160115','YYYYMMDD');

END IF

Concatenate(MyTable)

LOAD

"COL1",

    "COL2";

SQL SELECT *

FROM "MY_TABLE" where trunc(DATE)>=to_date('20160101','YYYYMMDD') and trunc(DATE)<=to_date('20160115','YYYYMMDD')

and 'DATE' > '$(lastReloadTime)';

store MyTable into [pathToQVD FILE];

[Anonymous]

Hi Razvan

Your code above is having issue in Where clause. I would suggest to use the following algorithm –

1.    Check whether QVD file already exist
2.    IF QVD File does not Exist GOTO Step – 8
3.    Get the Max Load Date from QVD file
4.    Load Records from Database table Where Date > Max Load Date
5.    Concatenate Load records from existing QVD File
6.    Store table to same QVD file
7.    GOTO Step – 10
8.    Load All records from Database table
9.    Store Table to the QVD file
10.    Drop Table from Memory

Please refer the example below –

let vFileExists = 0;

FOR EACH File IN ([pathToQVD FILE])

let vFileExists = 1;

NEXT File

IF $(vFileExists) = 1 then

Last_Load_Date:
LOAD
MaxString(DATE) as MaxDate
FROM [pathToQVD FILE](qvd);

let vMaxDate= peek('MaxDate',0,'Last_Load_Date');

MyTable:

Load

DATE

, COL1

, COL2;

SELECT DATE, COL1, COL2 from "MY_TABLE" where trunc(DATE)>'$(vMaxDate)';

CONCATENATE
Load

DATE

, COL1

, COL2;

FROM [pathToQVD FILE](qvd);

ELSE

            MyTable:

Load

DATE

, COL1

, COL2;

SELECT DATE, COL1, COL2 from "MY_TABLE" where trunc(DATE)>'$(vMaxDate)';

END IF

store MyTable into [pathToQVD FILE];

DROP Table MyTable ;

[razvan_brais]

Hy Vinod ,

Thank a lot for your reply. In mean time I`ve made it with the script I wrote , the problem was in my where clause.

But now I want to do something else. Because the data is massive , and my qvd is bigger than 1 GB , I would like to save my data into qvd by periods. When I say period , I would like to make a new qvd file  every 3 months. For example in my DATA field I have info that starts from 01-JAN-2016 and ends ,let`s say today. So I would like to create a qvd file something like this:

if(DATA>=01-JAN-2016 & DATA <01-APR-2016) store in FILE_JAN_MAR.qvd

else

if(DATA>=01-APR-2016 & DATA <01-JUL-2016)store in FILE_APR_JUL.qvd

and so on.

And the reloading of this qvd files should be done for all of them.

How can I achive this type of saving and reloading?

And I have one more problem. Because there is a lot of information , my app is a bit slower. How can I make it faster.

Thank in advance.