7 Replies Latest reply: Jun 9, 2015 3:49 PM by Jordi Camps RSS

    BINARY load with dynamic file name

    Jordi Camps

      Hi,

       

      I have an environment where a UX.QVW needs to import the data structure from a Data.QVW using a binary load.

       

      That would be very easy if the QlikMart file always had the same name - but it doesn't. The reasons are a little harder to explain, but for the purpose of simplicity, let's assume it's just a YearMonth suffix, for example Data_201505.QVW.

       

      I need to get the BIINARY LOAD code right. I cannot use any other script sentences before to calculate the filename, so I expect dollar sign to be the solution, but cannot get it right. Something like:

       

      BINARY QM_$(=year(today()*100+month(today())).qvw

       

      ... just does not work... it seems that the dollar sign with equal sign is not properly evaluated. It does work in an expression in a text box for example, but when debugging it in the load script, the $(=...) just evaluates to empty string or worse to '(internal error)'.

       

      There might already be a solution in the community but I cannot find it...

       

      Any ideas? Thanks in advance!

       

      - J

        • Re: BINARY load with dynamic file name
          Petter Skjolden

          You expression should work if you do this:

           

          BINARY_QM_$(=Year(Today())&Date(Today(),'MM')).qvw;

            • Re: BINARY load with dynamic file name
              Jordi Camps

              Hi Petter and thanks for the feedbackk, unfortunately it doesn't work, I'm consistently getting the same behavior in any expression I try with $(=...).

               

              If used in the binary sentence, a "file not found" error pops up, and the filename is just as if the $(=...) returned empty string, see:

              09-06-2015 21-23-01.jpg

               

              My original expression had the same behavior.

               

              I've tried putting it into a variable like so:

                   ///$tab Main

                   set x = BINARY_QM_$(=Year(Today())*100 + month(Today())).qvw;

               

              It does not work either, in the debugging trace is shows that the dollar sign evaluates to blank:

                   set x = BINARY_QM_.qvw

               

              Whereas if i put in a Text Box in a sheet, the same expression:

                   ='BINARY_QM_$(=Year(Today())*100 + month(Today())).qvw'

               

              I get the expected result:

                   BINARY_QM_201506.qvw

               

              Did I miss anything and the $(=...) expansion does NOT work on the script? If so... how can I achieve a binary load from dynamic filenames?

               

              Thanks,

            • Re: BINARY load with dynamic file name
              Massimo Grossi

              I think with another .qvw, all files in same dir

               

              a1.qvw           // make an include with a dynamic binary load

              DIRECTORY;

              let name = 'binary a_' & date(today(), 'YYYYMM') & '.qvw;';

              tmp: load 0 as [$(name)]

              AutoGenerate 0;

              store tmp into a1.inc (txt);

               

              output is a1.inc

              1.png

               

              a2.qvw           // binary load

              $(Must_Include=a1.inc);

                • Re: BINARY load with dynamic file name
                  Jordi Camps

                  Very nice trick / workaround Massimo!

                   

                  I elaborated on your idea, since having the binary load on an external INC file was not a very secure strategy (a missing or deleted file would result in a unsecure UX document)

                   

                  What I finally did is:

                  a) The DATA_YYYYMM.qvw creates, on its own reloading, the a1.inc file. (using Massimo's code)

                  b) On the hidden script of the UX, i'm doing a BINARY Data_$(=Must_include=a1.inc). That way if the file is wrong it just won't load at all, keeping security at bay.

                   

                  Thx,

                   

                  - J

                • Re: BINARY load with dynamic file name
                  Rob Wunderlich

                  I don't think the "=" within $() is a technique available in script. You can however reference a variable if you can pass the variable in.