Insert new records into QVD

[#1]

Hi,

I have created few QVD files.

Now i want to insert when there is a new rows added into my SQL tables.

i have a column in SQL table called "mmonth" and based on this i can identify the rows to be added into QVD.

mmonth

----------

201601

201602

201603

2016--

2016-- and so on...

can you please help me with the syntax that i will use to insert monthlly data into my qvd files.

i have used the below syntax for qvd.

LIB CONNECT TO 'SA(ent_prk)';

ActivityData:

LOAD fActivity,

    CoCd,

    CoCdFlag,

    mmonth,

SQL SELECT fActivity,

    CoCd,

    CoCdFlag,

    mmonth,

FROM "SA".dbo.ActivityData;

STORE ActivityData INTO 'lib://AF (ent_prk)/ActivityData.qvd';

Thanks

Prasanta

[swuehl]

May I just refer to my answer in a recent thread:

Re: Store in QVD with Incremenatal Load

[amit_saini]

Hi,

Please go through the attached doc.

Thanks,

AS

[#4]

Hi Prasanta,

Try this:

Last_updated_date:

load max(mmonth) as MaxMonth

Resident ActivityData;

Let Last_updated_date=peek('MaxDate',0,'Last_updated_date');   //Create a variable to select maximum date in QVD

Drop Table incremental_insert_only;

Incremental:

LOAD fActivity,

    CoCd,

    CoCdFlag,

    mmonth,

SQL SELECT fActivity,

    CoCd,

    CoCdFlag,

    mmonth,

FROM "SA".dbo.ActivityData

where mmonth>$(Last_updated_date);

Concatenate

Load

fActivity,

    CoCd,

    CoCdFlag,

    mmonth

from 'lib://AF (ent_prk)/ActivityData.qvd';

STORE Incremental INTO 'lib://AF (ent_prk)/ActivityData.qvd';

Drop table Incremental;

Load

fActivity,

    CoCd,

    CoCdFlag,

    mmonth

from 'lib://AF (ent_prk)/ActivityData.qvd';

Check this document:

Incremental Load.docx

Regards,

Rohan

[robert99]

Incremental load. One approach is shown below

The initial App extracted a full load from Call (repair call) 410000 on (run overnight).  And then saved this to a QVD

SCCallTess410Kon.qvd] (qvd);

========================================================================

This next section (done using another App) is to incrementally load (during the day) where records were added, changed and deleted. And then to update the above QVD.

You need a latest update timestamp and a unique record number (Call) for every row. This might need concatenated (joined) fields.

TO ENTER THE START CALL NUMBER TO LOAD (NEEDED WHEN DELETE RECORDS)

LET vCallUpperOld = 410000 ; // start call number when delete records

TO GET THE LATEST DATE FROM THE QVD

CallReloadMax: 

load

MAX (CallLastUpdate) AS CallReloadMax   

FROM [lib://QSQVDs/SCCallTess410Kon.qvd]

(qvd);

LET vDateIncremLoad = num(  PEEK ('CallReloadMax',-1,'CallReloadMax'))  ; // can include -1

drop table CallReloadMax;

DO THE INCREMENTAL LOAD

• //  Load the NEW data from the last overnight load or incremental update

SCCallTess:

LOAD

* 

;

SQL SELECT

  *  //or list fields

FROM MERRYCHEFSC5LIVE.dbo.SCCall

where "CallLastUpdate" >= $(vDateIncremLoad) -1;  //the minus 1 is not necessary but can do this //to ensure Qlik has picked up all the recent record

• ///----------------------load QVD

SCCallTess:

Concatenate (SCCallTess)

LOAD

*

FROM

[lib://QSQVDs//SCCallTess410Kon.qvd]

(qvd)

where not exists (CallNum); // this “where not” step excludes changed (duplicated) records

• //////------------RemoveDeletedCalls (only needed if delete records)

SCTessInner:

Inner Join (SCCallTess)   //this must be an inner join

SQL SELECT

    "CallNum"   //unique record

FROM MERRYCHEFSC5LIVE.dbo.SCCall

where "CallNum" >= $(vCallUpperOld)

;

• //---------Overwrite QVD-----------------------------

If ScriptErrorCount = 0 then

STORE SCCallTess INTO

[lib://QSQVDs//SCCallTess410Kon.qvd]

(qvd);

End If

[Anonymous]

Hi prasanta ,

check below link

http://www.analyticsvidhya.com/blog/2014/09/qlikview-incremental-load/

[#7]

Hello Everyone,

thanks for your help:

so far i have tried to write the below for incremental load.

but still not sure if i am doing the right steps. as i am not able to understand why its fetching entire data set everytime.

i believe it should fetch only new data into qvd.

Please refer the below load script and image for data load status

// this is my existing data in qvd

Final:

LOAD

    max(fMonth) as MaxMonth

FROM [lib://AccountProfitability (ent_prk)/Centertable.qvd]

(qvd);

let vMaxMonth = Peek('MaxDate',0,'Final');

DROP Table Final;

// load from SQL for new data

LIB CONNECT TO 'Uti (ent_prk)';

Deliv:

LOAD Deliv,

    "Deliv Item",

    fMonth;

SQL SELECT Deliv,

    "Deliv Item",

    fMonth

FROM PPT.dbo.Centertable

where fMonth >'$(vMaxMonth)';

CONCATENATE

LOAD

    Deliv,

    "Deliv Item",

    fMonth

FROM [lib://AF (ent_prk)/Centertable.qvd]

(qvd);

store Deliv into 'lib://AF (ent_prk)/Centertable.qvd';

Drop Table Deliv;

LOAD

    Deliv,

    "Deliv Item",

    fMonth

FROM [lib://AF (ent_prk)/Centertable.qvd]

(qvd);

[niceqlik]

Hi Prasanta,

I think you are refering different resources while retrieving the maximum date (Final table) and storing the result set into same qvd.

FROM [lib://AccountProfitability (ent_prk)/Centertable.qvd]

FROM [lib://AF (ent_prk)/Centertable.qvd]

[Anonymous]

Hi Prasantha ,

Debug the script and check whether you are getting correct values for Fmonth and variable vMaxMonth .

[#10]

let vMaxMonth = Peek('MaxDate',0,'Final');

should be:

let vMaxMonth = Peek('MaxMonth',0,'Final');