7 Replies Latest reply: May 30, 2014 5:06 AM by Hartmut Kesper RSS

    Store Inputfield table data to QVD with button macro

    M Paeper

      Hi,

       

      Have created a simple Inputfield and button to store the table to qvd model to test a concept (attached), but am unclear on some aspects.

       

      1. What is a good way of saving model user input obtained using the "Inputfield" function for all users (I understand from the docs that inputfield input values are defined per user login and I actually want a global input field for all users)? I'll explain my approach so far below:

       

      I was thinking I'd use Inputfield to obtain the user data, then store the Inputfield and its primary keys to a QVD file and then load that QVD as input to another model later. However, I ran into trouble when trying to store just those fields, so ended up with trying to "export" the table instead.

       

      The data is meant to be an original value and then an updated value obtained through Inputfield, and then for later calculations / what if analyis etc I was thinking of using the Inputfield value if its >0 (i.e. it exists) otherwise use the original value.

       

      I know I can right click and -> export the table, but our users probably wont get that right consistently, so I wanted to do this the same way every time using a button via a macro.

       

      However, the macro code I located here works http://community.qlik.com/message/114143#114143 but only when I click on "test" in the macro editor and not when I click on the button. Also I need to add more functionaliity to the macro:

       

      Our users select certain fields and "lock" those fields and may forget to unlock them which means that even if the button store to qvd worked it wouldnt necessarily be saving the entire table.

       

      2. How can I save the full table (actually I'd prefer to store only the primary keys and Inputfield if possible and not all fields) even if user selections are in force. Alternatively how can I unlock all locked fields and also clear all selections in a the macro before storing the table to QVD?

       

      Am running QVx64 11.2

       

      3. I'm using a button macro to export the Table to a QVD. I have enabled system access and drive e:\ is a valid path. This script as I said works (a correct QVD file is created) when I click on the "test" button in "Edit module" but clicking on the "save input" button in the model doesnt work.

       

      sub SaveTableToFile

      set obj = ActiveDocument.GetSheetObject("CH01")

      obj.ExportEx "e:\CH1inputs.qvd", 4

      end sub

       

      Thanks

       

      Cheers

        • Re: Store Inputfield table data to QVD with button macro
          whiteline _

          Hi.

           

          There is no global inputfield values.

          2. How can I save the full table (actually I'd prefer to store only the primary keys and Inputfield if possible and not all fields) even if user selections are in force. Alternatively how can I unlock all locked fields and also clear all selections in a the macro before storing the table to QVD?

          You could create a hidden chart for export purposes with expressions that display values regardless of user selections (using set analysis).

           

          Have you assigned the external action for that button to run a macro SaveTableToFile ?

            • Re: Store Inputfield table data to QVD with button macro
              M Paeper

              Hi Whiteline,

               

              Thanks for your feedback,

               

              Great idea on set analysis - I've only ever used it to filter fields but you have a point I could use it to select all as well. Hidden table ... that could be useful idea too to reduce screen clutter, and using it I could just have a table with only the key fields I want.

               

              As for external action I think I did what you describe but maybe I left out a step because its not working 100%.

               

              I did this.

              1. Created a new button object. Then for the button properties did this:

              2. Under tab-actions chose the "Run Macro" as action and "StoreCH1TableToQVD" as macro name.

              3. Under edit module entered the ffg code:

               

              sub SaveTableToFile

              set obj = ActiveDocument.GetSheetObject("CH01")

              obj.ExportEx "e:\CH1inputs.qvd", 4

              end sub

               

              Checked VBscript and allow system access under both security options.

               

              Everything else left as default.

               

              Am I missing anything?

               

              Thanks

            • Re: Store Inputfield table data to QVD with button macro

              Hi,

              as to "Alternatively how can I unlock all locked fields and also clear all selections in a the macro before storing the table to QVD?":

               

              set obj = ActiveDocument.GetSheetObject("CH01")

              ActiveDocument.ClearAll false 'deletes the filter

              obj.ExportEx "e:\CH1inputs.qvd", 4

              ActiveDocument.back 'resets the filter

               

               

              The frontend-user won't even realise that the filter has been removed for a moment unless the exported table is very large.