Skip to main content
Announcements
Qlik Community Office Hours, March 20th. Former Talend Community users, ask your questions live. SIGN UP
cancel
Showing results for 
Search instead for 
Did you mean: 
razvan_brais
Creator III
Creator III

Write data to qvd file and reload only new info

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];

2 Replies
Anonymous
Not applicable

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
Creator III
Creator III
Author

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.