9 Replies Latest reply: Mar 30, 2016 9:49 AM by Andrew Mein RSS

    Insert new records into QVD

    Prasanta Kumar

      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

        • Re: Insert new records into QVD
          Stefan Wühl

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

          Re: Store in QVD with Incremenatal Load

          • Re: Insert new records into QVD
            Amit Saini

            Hi,

             

            Please go through the attached doc.

             

            Thanks,

            AS

            • Re: Insert new records into QVD
              Rohan DSouza

              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

              • Re: Insert new records into QVD
                Robert Hutchings

                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

                • Re: Insert new records into QVD
                  Prasanta Kumar

                  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