Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

guitartrova
Contributor III

Incremental load in QVD loading new and updated records

Hello community, I have the following challenge: I need to upload my data incrementally and add new ones and update existing records.


Current Script


vDate = Date(Date#('20180701','YYYYMMDD'),'YYYYMMDD'); 

DO WHILE vDate <= Today(1) 

  /* TRACE */ SELECT * FROM TABLE WHERE DATE = TO_DATE('$(vDate)','YYYYMMDD'); 

  LET vDate = Date(Date#('$(vDate)', 'YYYYMMDD')+1 ,'YYYYMMDD'); 

LOOP


STORE VENTA_GC_2018 into [lib://QVDS/]VENTA_GC_2018.qvd;

//COMBINE NEW DATA WITH EXISTING QVD                  

CONCATENATE (VENTA_GC_2018)

           LOAD *

           FROM [lib://QVDS/VENTA_GC_2018.QVD] (QVD)

WHERE NOT EXISTS(NIS_RAD)

2 Replies
JustinDallas
Valued Contributor

Re: Incremental load in QVD loading new and updated records

Here is how I do my incremental load since the full table is about 14 million rows and takes forever.  The one drawback is that I DO NOT add in updated records.  This script could be more genericized to make it more robust.

LET vQVDexists = NOT ISNULL(QVDCreateTime('lib://blahblah/v3\ExpediteAuditExtract.qvd')); 

LET vNow = Now();

IF $(vQVDexists) THEN;

TRACE('QVD Exists, so we are going to append');

    LatestUpdate: 

    LOAD  Date(Max(updated_dt)) as MaxDate

    FROM [lib://blahblah/v3\ExpediteAuditExtract.qvd]

    (qvd)

    ;

Trace('Got to the LatestUpdate');

   

    LET vMaxDate = Peek('MaxDate',0,'LatestUpdate');

    TRACE('Max update' & '$(vMaxDate)');

   

    NewData:

    LOAD *

    ;

    SQL SELECT stuff

    FROM "TableOfInterest"

    WHERE updated_dt >  '$(vMaxDate)'

     ;

Trace('Concatenating the new data with the old data');

    Concatenate(NewData)

    LOAD * FROM [lib://blahblah/v3\ExpediteAuditExtract.qvd]

    (qvd)

    ;

    STORE NewData into [lib://blahblah/v3\ExpediteAuditExtract.qvd] (qvd);

    DROP Tables NewData, LatestUpdate

    ;

  ELSE

  Trace('QVD does not exist, so we are doing a full load');

    DATA:

    LOAD everything...

;

SQL SELECT everything FROM...

;

    STORE DATA into [lib://blahblah/v3\ExpediteAuditExtract.qvd] (qvd);

    DROP Table DATA

    ;

END IF

;

This doesn't remove stale records and bring in updated records.  But I believe this could be done using the whole "WHERE NOT EXISTS ('somekey','somekey')" construct where you don't concatenate the old records if their primary key already exists.

Hope this helps.

guitartrova
Contributor III

Re: Incremental load in QVD loading new and updated records

It still does not work, it does not add all the records, just a few.

Community Browser