20 Replies Latest reply: Feb 13, 2014 9:52 AM by Nicole Smith RSS

    update qvd and append

    m m

      Hi Everyone.

       

      I have a qvd with following type of records.

       

      ID     Name     Amount

      123     james     $300

      127     John      $234

      547     Alex        $555

       

       

      New file from sales comes in daily basis with new and corrected records. Therefore I want to update any corrected records and append new records in above qvd.

       

      for example today's sales file contains

       

      ID         Name     Amount

      123      James     $265

      432     Judy         $800

       

       

      How do you handle above scenerio. Please share example qvw.

       

      thanks

        • Re: update qvd and append
          Nicole Smith

          Load script that looks like the following should do the trick (first load in your new data, then the qvd--the where not exists phrase is what is going to give you what you need):

          Data:

          LOAD ID,

               Name,

               Amount

          FROM NewData.csv (txt, codepage is 1252, embedded labels, delimiter is ',', msq);

           

          CONCATENATE (Data)

          LOAD ID,

               Name,

               Amount

          FROM QVD.qvd (qvd)

          WHERE not exists(ID);

           

          STORE * FROM Data into QVD.qvd;

          Example files are also attached.

          • Re: update qvd and append
            Ajay Krishnan Prabhakaran

            Hi,

            Cases like these are done through incremental loads. They are very handy. Check out the cookbook by The specified item was not found. its amazing and has all the tools you need. It has a working qvw with clear comments that could be used for appending, updating and deleting records.

            Downloads - Rob Wunderlich Qlikview Consulting

             

            Thanks

            AJ

            • Re: update qvd and append
              Michael Gardner

              Edit:  Ignore my solution below.  I interpreted Nicole's solution wrong.

               

               

               

              TempData:

              LOAD ID,

                   Name,

                   Amount

              FROM QVD.qvd (qvd);

               

               

              LEFT JOIN

              LOAD ID,

                   Name as NewName,

                   Amount as NewAmount

              FROM NewData.csv (txt, codepage is 1252, embedded labels, delimiter is ',', msq);

               

              //This is to update the data


              Data:

              LOAD ID,

                   IF(Len(NewName>0),NewName,Name) AS Name,

                   IF(Len(NewAmount>0),NewAmount,Amount) AS Amount

              RESIDENT TempData;


              DROP TABLE TempData;



              • Re: update qvd and append
                Srikanth P

                Hi dd ddd, Just try the Incremental load like Nicole suggested

                • Re: update qvd and append
                  sunil kumar

                  Try this and  add  one more column LastUpdateDate , Do increamental Load.


                  //Initial Reload & generate Qvd

                  ProdMaster:

                  LOAD ID,

                       Name,

                       Amount

                       LastUpdateDate

                  FROM

                  Sample_Data.xls

                  (biff, embedded labels, table is [Sample_Data$]);

                   

                  Store ProdMaster into [..\Data\Qvds\ProdMaster.Qvd];

                  DROP Table ProdMaster;

                  //exit script;

                  Max_LastUpdateDate:

                  LOAD

                       Max(ID) As Max_ID

                  FROM

                  [..\Data\Qvds\ProdMaster.Qvd]

                  (qvd);

                   

                  //exit script;

                  Let  vMaxID = peek('Max_ID',0,'Max_LastUpdateDate');

                  Drop Table Max_LastUpdateDate;

                  Complete_Data:

                  LOAD ID,

                       Name,

                       Amount

                      LastUpdateDate

                  FROM

                  [..\Data\Qvds\ProdMaster.Qvd]

                  (qvd);

                  Concatenate

                  LOAD ID,

                       Name,

                      Amount

                      LastUpdateDate

                  FROM

                  Sample_Data.xls

                  (biff, embedded labels, table is [Sample_Data$])

                  where ID>'$(vMaxID)';

                  Store Complete_Data into [..\Data\Qvds\ProdMaster.Qvd];



                  Regards

                  Sunil

                    • Re: update qvd and append
                      m m

                      Hi Sunil Kumar

                       

                      How is your solution better or different then one suggested by Nicole. Please explain.

                        • Re: update qvd and append
                          sunil kumar

                            my solution  works only  when you want to append the data not  to udate records,Please ignore my solution best use is where not clause Exist is the best solution .

                            • Re: update qvd and append
                              m m

                              I have follow-up question. what if there is more then 1 uniq key. I might have to create a composite key. how would I use composite key to do append

                               

                              WHERE not exists(Should I put composite key here);



                              another question.


                              i get error when i do following:  Field Not found


                              empData:

                              LOAD ID,

                                   Name,

                                   Amount,

                              LOADID & | & Name as uniqkey

                              FROM QVD.qvd (qvd);

                                • Re: update qvd and append
                                  Srikanth P

                                  Hi Try something like below:

                                   

                                  First check the QVD exists, If QVD exists, do Incremental Load else full load from file.

                                   

                                  LET vQVDExists = IF(FILESIZE('qvdata\QVD.qvd')>0 , -1 , 0) ;

                                   

                                  IF $(vQVDExists) THEN //QVD Exists do Inc Load

                                       TABLEA:

                                       LOAD * , LOADID&'|'&NAME AS UNIQUEKEY FROM SOURCEEXCELFILE ; // SorceFile

                                       LOAD * FROM [qvdata\QVD.qvd] (qvd) WHERE Not Exists(UNIQUEKEY) ; // QVD

                                  ELSE //QVD does not Exists do Full Load

                                       TABLEA:

                                       LOAD * , LOADID&'|'&NAME AS UNIQUEKEY FROM SOURCEEXCELFILE;

                                  ENDIF

                                   

                                  STORE TABLEA INTO [qvdata\QVD.qvd] (qvd) ;

                                   

                                  DROP Table TABLEA ;

                                    • Re: update qvd and append
                                      m m

                                      Hi Dathu

                                       

                                      1) I dont need to check for file exist or not

                                      2) your code does not have any concatenate to update and append.

                                       

                                      Nicole solution worked for me however I require further modification.

                                       

                                      1) I will require composite key to be used. question is can i used composite key in where not exist.

                                       

                                      2) I was having issue with field not found. with following code.

                                      empData:

                                      LOAD ID,

                                           Name,

                                           Amount,

                                      LOADID & | & Name as uniqkey

                                      FROM QVD.qvd (qvd);

                                        • Re: update qvd and append
                                          Srikanth P

                                          Hi , If the two tables have same structure (same field names) then table will be concatenate automatically.

                                          So in my Example, eventhogh, I didn't mention the concatenate, the table will be concatenate because both tables have the same structure.

                                           

                                          In your case, Please try like below:

                                          empData:

                                          LOAD ID,

                                               Name,

                                               Amount,

                                               ID & '|' & Name as uniqkey

                                          FROM QVD.qvd (qvd);


                                          I believe, you are making composite key on ID & Name, then above script will work.

                                      • Re: Re: update qvd and append
                                        Nicole Smith

                                        You would do a composite key with a where not exists clause like below.  I am assuming your composite key is made up of ID and Name, so you may need to change the fields.  I have edited the original load script that I have given you.

                                        Data:

                                        LOAD ID & '|' & Name as CompositeKey,

                                            ID,

                                            Name,

                                            Amount

                                        FROM NewData.csv (txt, codepage is 1252, embedded labels, delimiter is ',', msq);

                                         

                                        CONCATENATE (Data)

                                        LOAD ID & '|' & Name as CompositeKey,

                                            ID,

                                            Name,

                                            Amount

                                        FROM QVD.qvd (qvd)

                                        WHERE not exists(CompositeKey, ID & '|' & Name);

                                         

                                        STORE * FROM Data into QVD.qvd;

                                • Re: update qvd and append
                                  m m

                                  Thanks Nicole.

                                   

                                  I moved away from your solution since I was not getting result due to composite key issue however I will give it a try again. this method if it works then better because i don't have to create separate qvd with key to join later.

                                   

                                  Another point is that my composite key will be made of 3 fields   ID&Name&StoreNo  another field I have in the script.

                                   

                                  Can you please explain what is the difference between.

                                   

                                  WHERE not exists(ID);

                                   

                                  vs

                                   

                                  WHERE not exists(CompositeKey, ID & '|' & Name);

                                   

                                   

                                  How does program internally interpret above 2 statement.

                                    • Re: update qvd and append
                                      Nicole Smith

                                      WHERE not exists(ID) looks where the field ID was loaded in a previous table and only loads data in the current table (with the where clause) where the ID is not in the previously loaded ID field.

                                       

                                      WHERE not exists(CompositeKey, ID & '|' & Name) does the same thing, we just have to write it a bit differently.  CompositeKey was loaded in a previous table, and we are just creating the composite key in this table, so we need to use ID & '|' & Name instead (since that is what the composite key is made up of--in my example anyway).

                                       

                                      If you look up exists() in QV help, it gives a similar example using Employee and FirstName & ' ' &  LastName.