Skip to main content
Announcements
See what Drew Clarke has to say about the Qlik Talend Cloud launch! READ THE BLOG
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Insert new records into QVD

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

9 Replies
swuehl
MVP
MVP

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

Re: Store in QVD with Incremenatal Load

amit_saini
Master III
Master III

Hi,

Please go through the attached doc.

Thanks,

AS

Not applicable
Author

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
Specialist III
Specialist III

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
Not applicable
Author

Not applicable
Author

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

desk.JPG

niceqlik
Partner - Contributor III
Partner - Contributor III

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
Not applicable
Author

Hi Prasantha ,

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

Not applicable
Author

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


should be:


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