Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Anonymous
Not applicable

Incremental reload and store into QVD file

Hi all,

To find out how incremental reload and storing into a QVD file works, I made an example document (find it attached).

It works fine, but the exact way it works is unclear to me. Maybe one of you can help me out.

Lets assume that there is a lot data in the database I would like to load. Because of that, I would like to load the data once. After that, only new rows will be inserted into the QVD file. But, ss this the way my example works? It seems to me, that all the data will be load during a reload of the document, and not only new rows will be inserted but a whole new load will be stored into the QVD. Is that right? If so, how can I make it work so that only new rows will be inserted?

I hope someone can explain the me.

Kind regards,

Henco

1 Solution

Accepted Solutions
jagan
Luminary Alumni
Luminary Alumni

Hi,

Use this script

LET lastReloadTime = ReloadTime();

GA_DATA:

LOAD

    PK,

    Calendardate,

    Revenue

FROM (qvd);

CONCATENATE

LOAD

    PK,

    Calendardate,

    Revenue

FROM

(ooxml, embedded labels, table is Sheet1)

WHERE (Calendardate > $(lastReloadTime));

store GA_DATA into ;

Hope this helps you.

Regards,

Jagan.

View solution in original post

11 Replies
jagan
Luminary Alumni
Luminary Alumni

Hi,

Use this script

LET lastReloadTime = ReloadTime();

GA_DATA:

LOAD

    PK,

    Calendardate,

    Revenue

FROM (qvd);

CONCATENATE

LOAD

    PK,

    Calendardate,

    Revenue

FROM

(ooxml, embedded labels, table is Sheet1)

WHERE (Calendardate > $(lastReloadTime));

store GA_DATA into ;

Hope this helps you.

Regards,

Jagan.

Not applicable
Author

Hi Henco,

please see also QV Help:  Using QVD Files for Incremental Load

Good Luck!

Rainer

jagan
Luminary Alumni
Luminary Alumni

Hi,

Check this URL

http://community.qlik.com/message/68515#68515

Regards,

Jagan.

Anonymous
Not applicable
Author

Thanks for your quick reply. I think I understand what you mean and that will be helpful.

There is one thing I am worried about. At first the document loads the data from the database, to store it into the QVD file:

DATA:

LOAD PK,

     Calendardate,

     Revenue

FROM

(ooxml, embedded labels, table is Sheet1);

store DATA into ;

What happens when I reload the document? Is it going to do a full reload and store all the data into the qvd file? Because that is exactly something I want to avoid.

I read the other question you linked to, but to sure I ask this question.

Kind regards,

Henco

jagan
Luminary Alumni
Luminary Alumni

Hi,

You need to check whether QVD exists, if QVD exists then load from QVD, otherwise load data from Datasource.

You need to restrict Full reload by this way. 

LET lastReloadTime = ReloadTime();

If qvdcreatetime("QVDFilePath") Then

GA_DATA:

LOAD

    PK,

    Calendardate,

    Revenue

FROM (qvd);

ELSE

GA_DATA:

LOAD PK,

     Calendardate,

     Revenue

FROM

(ooxml, embedded labels, table is Sheet1);

END IF

CONCATENATE (GA_DATA)

LOAD

    PK,

    Calendardate,

    Revenue

FROM

(ooxml, embedded labels, table is Sheet1)

WHERE (Calendardate > $(lastReloadTime));

store GA_DATA into ;

Hope this helps you.

Regards,

Jagan.

Anonymous
Not applicable
Author

Thanks again Jagan! This will really help me out.

Regards,

Henco

Not applicable
Author

Hi Jagan,

I tried both scripts as mentioned above, but got the same error:

Error in expression:

')' expected

CONCATENATE (GA_DATA)

LOAD

    PK,

    Calendardate,

    Revenue

FROM

(ooxml, embedded labels, table is Sheet1)

WHERE (Calendardate > 7-5-2013 18:37:59)

May I know how to solve this?

Not applicable
Author

Hi, I´ve been gettin the same error, I apparently solved it by replacing the let sentence for a set sentence,

when establishing the lastReloadTime variable.

Try it and let me know, because I´ve been getting some errors but I´ve been able to solve them one by one

Regards

Anonymous
Not applicable
Author

add code in the where clause

CalenderDate>date('$(lastReloadTime)','MM-DD-YYYY')

It will work.