4 Replies Latest reply: Apr 19, 2012 6:02 AM by melania.fedeli RSS

    Calling a script from a macro

      Hi Everyone,

       

      I need to provide a QV solution for the users which does the following:

      1. gets a value from a selector (say VALUE)

      2. deletes a set of rows from a sql server DB (delete from table where field = VALUE)

      3. deletes the same set of data from the relevant qvd file

      4. reloads the data.

       

      For doing so, I have thought I could use a button with a macro and I have found out how to connect to the sql server DB and delete the rows.

      However, I don't know how to reflect these changes on the .qvd file as well as I don't see a way of calling the store command from within a macro. Is this possible?

       

      Any other (maybe simpler) solution anyone can think of would be much appreciated.

       

      Thanks and Best Wishes,

      Melania

        • Calling a script from a macro
          jagan mohan rao appala

          Hi,

           

          Check this link hope it helps you.

           

          http://community.qlik.com/docs/DOC-2784

           

          Regards,

          Jagan.

            • Calling a script from a macro

              Hi Jagan,

               

              that's cool, thanks a mil.

               

              I also need to delete some data from the QVD file and I was wondering if it's possible to:

               

              1. delete the data from the QV logical tables in memory with something like

              SET Result = ActiveDocument.DynamicUpdateCommand ("DELETE Table FACT Where FAC_id_extraction = 20120316114852")

               

              and then

               

              2. execute a store command to store the content of the table (after deletion) into the qvd file but I haven't ound any examples of calling a QV script from within a macro (whereas the opposite is quite common - i.e. calling a macro from a script). Is there any chance I can invoke a store command from within a macro?

               

              Thanks a million,

              Melania

                • Calling a script from a macro
                  jagan mohan rao appala

                  Hi,

                   

                  I don't think it is easier, but we can do this by saving the deleted records IDs in a CSV or Excel file in macro, and during reload exclude this values by using Except().  Check script below

                   

                  DeletedIDs:

                  LOAD

                       FAC_id_extraction

                  FROM FileName;

                   

                  DATA:

                  LOAD

                  *

                  FROM FileName.qvd (qvd)

                  WHERE NOT EXISTS(FAC_id_extraction);

                   

                  STORE DATA INTO FileName.qvd;

                   

                  DROP TABLE DeletedIDs;

                   

                  Regards,

                  Jagan.

                    • Calling a script from a macro

                      Hi Jagan,

                       

                      I was thinking about calling the Releoad method from within the macro.

                       

                      However, this forces the re-execution of the entire reload script. Can I execute just a portion of the reload script and not the entire one?

                      If the answer is not, then I think I'll write a macro that does that:

                      - delete the rows from the SQL Server DB table

                      - execute a Reload from the macro

                       

                      I will also have to modify the script to perform an insert/delete/update operation (as explained in the QV reference manual - using qvd for incremental load - case 4).

                       

                      I was just trying to avoid to re-execute the entire script just to align the content of a qvd file with the database table after a few rows have been deleted.

                       

                      Thanks a million and Best Wishes,

                      Melania