Qlik Community

QlikView Creating Analytics

Discussion Board for collaboration related to Creating Analytics for QlikView.

hencovanee
Contributor II

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
MVP
MVP

Re: Incremental reload and store into QVD file

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.

11 Replies
MVP
MVP

Re: Incremental reload and store into QVD file

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

Re: Incremental reload and store into QVD file

Hi Henco,

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

Good Luck!

Rainer

MVP
MVP

Incremental reload and store into QVD file

Hi,

Check this URL

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

Regards,

Jagan.

hencovanee
Contributor II

Incremental reload and store into QVD file

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

MVP
MVP

Incremental reload and store into QVD file

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.

hencovanee
Contributor II

Incremental reload and store into QVD file

Thanks again Jagan! This will really help me out.

Regards,

Henco

Not applicable

Re: Incremental reload and store into QVD file

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

Re: Incremental reload and store into QVD file

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

girishchhatani1
New Contributor III

Re: Incremental reload and store into QVD file

add code in the where clause

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

It will work.

Community Browser