17 Replies Latest reply: Apr 4, 2013 7:21 AM by Jonathan Dienst RSS

    Update edited/new QVD records from DB

    Bruce Tedder

      Hi All

       

      I have been trying to update my QVD with new or edited records on the DB unsuccessfully.  I have been using this script.  It siimply does not fetch and update the QVD with new or edited records.  Please help:

       

      //Fetch my exitsing data in my QVD

      RL_DATA:

      LOAD * FROM [C:\Users\tedder.bruce\Desktop\Qlikview Hirt & Carter\QVD\RL.QVD] (QVD);

       

      //Determine the Max (RL_EDIT_DATE) and store as vMaxEditDate

      RL_MaxDate:

      LOAD MAX(RL_EDIT_DATE)as MaxEditDate

      RESIDENT RL_DATA;

      let vMaxEditDate = Peek('MaxEditDate',0,'RL_MaxDate');

      DROP TABLE RL_DATA;

       

      //Determine the Max(RL_EDIT_TIME) from the records which have theMax(RL_ADD_DATE) and store as vMaxEditTime

      RL_DATA:

      LOAD Max(RL_EDIT_TIME) as RL_EDIT_TIME

      FROM [C:\Users\tedder.bruce\Desktop\Qlikview Hirt & Carter\QVD\RL.QVD] (QVD)

      WHERE RL_EDIT_DATE = $(vMaxEditDate);

      RL_MaxTime:

      LOAD Max(RL_EDIT_TIME) as MaxEditTime

      RESIDENT RL_DATA;

      let vMaxEditTime = Peek('MaxEditTime',0,'RL_MaxTime');

      DROP TABLE RL_DATA;

       

      //Not that I have the Max(RL_EDIT_DATE) and Max(RL_EDIT_TIME, I can use them to query the DB to return newer edited records.

      ODBC CONNECT32 TO PRISMHC (XUserId is XVRGBZVMBDZeGXRNZTbA, XPassword is EAHfPZVMBDZeGXRNZLfB);

      RL_New:

      SQL

      SELECT * FROM PWIN171.dbo.RL

      WHERE RL_EDIT_DATE > $(vMaxEditDate)

      and RL_EDIT_TIME > $(vMaxEditTime);

      CONCATENATE LOAD * FROM C:\Users\tedder.bruce\Desktop\Qlikview Hirt & Carter\QVD\RL.QVD (QVD)

      WHERE NOT EXISTS (ID);

      STORE RL_New INTO C:\Users\tedder.bruce\Desktop\Qlikview Hirt & Carter\QVD\RL.QVD;

      DROP TABLES RL_MaxDate,RL_MaxTime,RL_New;

        • Re: Update edited/new QVD records from DB
          Sushil Kumar

          Hi Try to give dates in quotes:

           

          WHERE RL_EDIT_DATE = '$(vMaxEditDate)';

           

           

          make change in other conditions too...

           

           

          HTH

          Sushil

          • Re: Update edited/new QVD records from DB
            Jonathan Dienst

            Hi

             

            I can see a couple of changes to get this to work.

             

            First, change the Peek expressions so that the variables contain correctly formatted date and time:

             

            ...

            let vMaxEditDate = Date(Peek('MaxEditDate',0,'RL_MaxDate'), 'YYYY-MM-DD');

            ...

            let vMaxEditTime = Time(Peek('MaxEditTime',0,'RL_MaxTime'), 'hh:mm:ss');

            ...

             

            (Use the correct date and time formats for your database. You can test this by manually submitting the SQL expression using a SQL front end like SQLMS or Toad)

             

            Then your Where condition looks suspect. I suggest that you use:

             

            SELECT * FROM PWIN171.dbo.RL

            WHERE RL_EDIT_DATE > '$(vMaxEditDate)' Or (RL_EDIT_DATE = '$(vMaxEditDate)' And RL_EDIT_TIME > '$(vMaxEditTime)')

             

            Hope that helps

            Jonathan

              • Re: Update edited/new QVD records from DB
                Bruce Tedder

                Thanks tried your changes and got this…note the RL_EDIT_TIME field is actually INT type, e.g.: 54501 would be 05:45:01 AM

                • Re: Update edited/new QVD records from DB
                  Bruce Tedder

                  Hi Guys (Thanks for the prompt responces)

                  I have used this as the RL_EDIT_TIME is int

                  let vMaxEditTime = Num(Peek('MaxEditTime',0,'RL_MaxTime'));

                   

                  This was the result.  Any ideas whats wrong with the syntax near 'CONCATENATE'?

                   

                    • Re: Update edited/new QVD records from DB
                      Jonathan Dienst

                      I am afraid I cannot see anything in the box/image.

                        • Re: Update edited/new QVD records from DB
                          Bruce Tedder

                          Hi Jonathan, Thanks for your help yesterday.  Re-ran the QVD refresh and it seemed to work.  The QVD seems to be missing 890 records versus when I use SQL to query the table in the DB.  I think the NOT EXISTS (ID) might be the issue? Should I simply not CONTCATENATE and Qlikview will update edited recorded and/or load new records? Please advise Johnathan.

                           

                          Below is the scrip which APPEARS to run fine:

                           

                          RL_DATA:

                          LOAD * FROM [C:\Users\tedder.bruce\Desktop\Qlikview Hirt & Carter\QVD\RL.QVD] (QVD);

                           

                          STORE RL_DATA INTO C:\Users\tedder.bruce\Desktop\Qlikview Hirt & Carter\QVD\RL_BACKUP.QVD;

                           

                          RL_MaxDate:

                          LOAD MAX(RL_EDIT_DATE)as MaxEditDate

                          RESIDENT RL_DATA;

                           

                          let vMaxEditDate = Date(Peek('MaxEditDate',0,'RL_MaxDate'), 'YYYY-MM-DD');

                           

                          DROP TABLE RL_DATA;

                           

                          RL_DATA:

                          LOAD Max(RL_EDIT_TIME) as RL_EDIT_TIME

                          FROM [C:\Users\tedder.bruce\Desktop\Qlikview Hirt & Carter\QVD\RL.QVD] (QVD)

                          WHERE RL_EDIT_DATE = '$(vMaxEditDate)';

                           

                          RL_MaxTime:

                          LOAD Max(RL_EDIT_TIME) as MaxEditTime

                          RESIDENT RL_DATA;

                           

                          let vMaxEditTime = Num(Peek('MaxEditTime',0,'RL_MaxTime'));

                           

                          DROP TABLE RL_DATA;

                           

                          ODBC CONNECT32 TO PRISMHC (XUserId is XVRGBZVMBDZeGXRNZTbA, XPassword is EAHfPZVMBDZeGXRNZLfB);

                          RL_New:

                          SQL

                          SELECT * FROM PWIN171.dbo.RL (NOLOCK)

                          WHERE RL_EDIT_DATE > '$(vMaxEditDate)' Or (RL_EDIT_DATE = '$(vMaxEditDate)' And RL_EDIT_TIME > '$(vMaxEditTime)');

                           

                          CONCATENATE LOAD * FROM C:\Users\tedder.bruce\Desktop\Qlikview Hirt & Carter\QVD\RL.QVD (QVD)

                          WHERE NOT EXISTS (ID);

                           

                          STORE RL_New INTO C:\Users\tedder.bruce\Desktop\Qlikview Hirt & Carter\QVD\RL.QVD;

                          DROP TABLES RL_MaxDate,RL_MaxTime,RL_New;

                          • Re: Update edited/new QVD records from DB
                            Bruce Tedder

                            Hi Jonathan, Thanks for your help yesterday.  Re-ran the QVD refresh and it seemed to work.  The QVD seems to be missing 890 records versus when I use SQL to query the table in the DB.  I think the NOT EXISTS (ID) might be the issue? Should I simply not CONTCATENATE and Qlikview will update edited recorded and/or load new records? Please advise Johnathan.

                             

                            Below is the scrip which APPEARS to run fine:

                             

                            RL_DATA:

                            LOAD * FROM [C:\Users\tedder.bruce\Desktop\Qlikview Hirt & Carter\QVD\RL.QVD] (QVD);

                             

                            STORE RL_DATA INTO C:\Users\tedder.bruce\Desktop\Qlikview Hirt & Carter\QVD\RL_BACKUP.QVD;

                             

                            RL_MaxDate:

                            LOAD MAX(RL_EDIT_DATE)as MaxEditDate

                            RESIDENT RL_DATA;

                             

                            let vMaxEditDate = Date(Peek('MaxEditDate',0,'RL_MaxDate'), 'YYYY-MM-DD');

                             

                            DROP TABLE RL_DATA;

                             

                            RL_DATA:

                            LOAD Max(RL_EDIT_TIME) as RL_EDIT_TIME

                            FROM [C:\Users\tedder.bruce\Desktop\Qlikview Hirt & Carter\QVD\RL.QVD] (QVD)

                            WHERE RL_EDIT_DATE = '$(vMaxEditDate)';

                             

                            RL_MaxTime:

                            LOAD Max(RL_EDIT_TIME) as MaxEditTime

                            RESIDENT RL_DATA;

                             

                            let vMaxEditTime = Num(Peek('MaxEditTime',0,'RL_MaxTime'));

                             

                            DROP TABLE RL_DATA;

                             

                            ODBC CONNECT32 TO PRISMHC (XUserId is XVRGBZVMBDZeGXRNZTbA, XPassword is EAHfPZVMBDZeGXRNZLfB);

                            RL_New:

                            SQL

                            SELECT * FROM PWIN171.dbo.RL (NOLOCK)

                            WHERE RL_EDIT_DATE > '$(vMaxEditDate)' Or (RL_EDIT_DATE = '$(vMaxEditDate)' And RL_EDIT_TIME > '$(vMaxEditTime)');

                             

                            CONCATENATE LOAD * FROM C:\Users\tedder.bruce\Desktop\Qlikview Hirt & Carter\QVD\RL.QVD (QVD)

                            WHERE NOT EXISTS (ID);

                             

                            STORE RL_New INTO C:\Users\tedder.bruce\Desktop\Qlikview Hirt & Carter\QVD\RL.QVD;

                            DROP TABLES RL_MaxDate,RL_MaxTime,RL_New;

                            • Re: Update edited/new QVD records from DB
                              Bruce Tedder

                              Hi Jonathan, Thanks for your help yesterday.  Re-ran the QVD refresh and it seemed to work.  The QVD seems to be missing 890 records versus when I use SQL to query the table in the DB.  I think the NOT EXISTS (ID) might be the issue? Should I simply not CONTCATENATE and Qlikview will update edited recorded and/or load new records? Please advise Johnathan.

                               

                              Below is the scrip which APPEARS to run fine:

                               

                              RL_DATA:

                              LOAD * FROM [C:\Users\tedder.bruce\Desktop\Qlikview Hirt & Carter\QVD\RL.QVD] (QVD);

                               

                              STORE RL_DATA INTO C:\Users\tedder.bruce\Desktop\Qlikview Hirt & Carter\QVD\RL_BACKUP.QVD;

                               

                              RL_MaxDate:

                              LOAD MAX(RL_EDIT_DATE)as MaxEditDate

                              RESIDENT RL_DATA;

                               

                              let vMaxEditDate = Date(Peek('MaxEditDate',0,'RL_MaxDate'), 'YYYY-MM-DD');

                               

                              DROP TABLE RL_DATA;

                               

                              RL_DATA:

                              LOAD Max(RL_EDIT_TIME) as RL_EDIT_TIME

                              FROM [C:\Users\tedder.bruce\Desktop\Qlikview Hirt & Carter\QVD\RL.QVD] (QVD)

                              WHERE RL_EDIT_DATE = '$(vMaxEditDate)';

                               

                              RL_MaxTime:

                              LOAD Max(RL_EDIT_TIME) as MaxEditTime

                              RESIDENT RL_DATA;

                               

                              let vMaxEditTime = Num(Peek('MaxEditTime',0,'RL_MaxTime'));

                               

                              DROP TABLE RL_DATA;

                               

                              ODBC CONNECT32 TO PRISMHC (XUserId is XVRGBZVMBDZeGXRNZTbA, XPassword is EAHfPZVMBDZeGXRNZLfB);

                              RL_New:

                              SQL

                              SELECT * FROM PWIN171.dbo.RL (NOLOCK)

                              WHERE RL_EDIT_DATE > '$(vMaxEditDate)' Or (RL_EDIT_DATE = '$(vMaxEditDate)' And RL_EDIT_TIME > '$(vMaxEditTime)');

                               

                              CONCATENATE LOAD * FROM C:\Users\tedder.bruce\Desktop\Qlikview Hirt & Carter\QVD\RL.QVD (QVD)

                              WHERE NOT EXISTS (ID);

                               

                              STORE RL_New INTO C:\Users\tedder.bruce\Desktop\Qlikview Hirt & Carter\QVD\RL.QVD;

                              DROP TABLES RL_MaxDate,RL_MaxTime,RL_New;

                                • Re: Update edited/new QVD records from DB
                                  Jonathan Dienst

                                  Bruce

                                   

                                  The Not Exists(ID) will block loading duplicate values of ID, so if those values have IDs that are duplicates, then the not exists is the problem. If you need to bring these in, then you will need to create a temporary ID field to control the load.

                                   

                                  In your SELECT....

                                   

                                       SQL SELECT *,

                                            ID As T_ID

                                        FROM   ....

                                      

                                  And in your QVD load,

                                   

                                       LOAD ... WHERE Not Exists(T_ID, ID);

                                   

                                  (This will break the optimised load for the QVD)

                                   

                                  Regards

                                  Jonathan

                                    • Re: Update edited/new QVD records from DB
                                      Bruce Tedder

                                      Hi Jonathan

                                       

                                      Sorry for being such a dubm-ass.

                                      I seem to be duplicating records now. I need to REPLACE the records in the QVD with records from the SQL SELECT where the ID matches AND add any that dont EXIST in the QVD.  Any ideas please?

                                       

                                      My Query is below:

                                      QM_DATA:

                                      LOAD * FROM [C:\Users\tedder.bruce\Desktop\Qlikview Hirt & Carter\QVD\QM.QVD] (QVD);

                                      STORE QM_DATA INTO C:\Users\tedder.bruce\Desktop\Qlikview Hirt & Carter\QVD\QM_BACKUP.QVD;

                                       

                                      QM_MaxDate:

                                      LOAD MAX(QM_EDIT_DATE)as MaxEditDate

                                      RESIDENT QM_DATA;

                                       

                                      let vMaxEditDate = Date(Peek('MaxEditDate',0,'QM_MaxDate'), 'YYYY-MM-DD');

                                      DROP TABLE QM_DATA;

                                       

                                      QM_DATA:

                                      LOAD Max(QM_EDIT_TIME) as QM_EDIT_TIME

                                      FROM [C:\Users\tedder.bruce\Desktop\Qlikview Hirt & Carter\QVD\QM.QVD] (QVD)

                                      WHERE QM_EDIT_DATE = '$(vMaxEditDate)';

                                       

                                      QM_MaxTime:

                                      LOAD Max(QM_EDIT_TIME) as MaxEditTime

                                      RESIDENT QM_DATA;

                                       

                                      let vMaxEditTime = Num(Peek('MaxEditTime',0,'QM_MaxTime'));

                                       

                                      DROP TABLE QM_DATA;

                                       

                                      ODBC CONNECT32 TO PRISMHC (XUserId is XVRGBZVMBDZeGXRNZTbA, XPassword is EAHfPZVMBDZeGXRNZLfB);

                                      QM_New:

                                      SQL

                                      SELECT * FROM PWIN171.dbo.QM (NOLOCK)

                                      WHERE QM_EDIT_DATE > '$(vMaxEditDate)' Or (QM_EDIT_DATE = '$(vMaxEditDate)' And QM_EDIT_TIME > '$(vMaxEditTime)');

                                       

                                      CONCATENATE LOAD * FROM C:\Users\tedder.bruce\Desktop\Qlikview Hirt & Carter\QVD\QM.QVD (QVD);

                                      //WHERE NOT EXISTS (ID);

                                       

                                      STORE QM_New INTO C:\Users\tedder.bruce\Desktop\Qlikview Hirt & Carter\QVD\QM.QVD;

                                      DROP TABLES QM_MaxDate,QM_MaxTime,QM_New;

                                        • Re: Update edited/new QVD records from DB
                                          Jonathan Dienst

                                          Bruce

                                           

                                          To do an incremental load to replace updated records, you need some way to identify the changed records, such as a last updated date/time field. You also need a unique key for each record (no duplicates). If your data does not meet these requirements, then you will not be able to update the QVD incrementally - you will have to do a full reload instead.

                                           

                                          If your data meets the above requirements, then you can incremental load with these steps:

                                           

                                          • Determine the last load time. This could be the maximum value of the last updated field in your QVD, or some other stored value indicating when the QVD was last refreshed, such as the QvdCreateTime function.
                                          • Load from the source all the records that have a last updated date greater than the last load time.
                                          • Load the date from the QVD, excluding those that were loaded from the source (because these records have been updated). Use Not(Exists()) as discussed earlier - but this does require that the ID field (or whatever key you choose) is unique for each record.
                                          • Store the updated QVD.

                                           

                                          I hope that helps. If you have more questions, I suggest that you open a new thread (referencing this thread if you like). This one has been marked as answered, so you are less likely to get more help here.

                                           

                                          Regards

                                          Jonathan

                                • Re: Update edited/new QVD records from DB
                                  Bruce Tedder

                                  Hi Guys (Thanks for the prompt responces)

                                  I have used this as the RL_EDIT_TIME is int

                                  let vMaxEditTime = Num(Peek('MaxEditTime',0,'RL_MaxTime'));

                                   

                                  This was the result.  Any ideas whats wrong with the syntax near 'CONCATENATE'?

                                  Error.JPG