5 Replies Latest reply: Apr 24, 2013 7:04 PM by Bryce St Clair RSS

    Passing a parameter file name to load

      Hello,

       

      I want to load my information from a file whose name can change over time:

       

      my data source is a file automatically by batch and his name is a function of time.

      is there a possibility that the script asks me the name of the file to load? and if possible ask me by presenting the name of the old file was loaded.

       

      thank you

        • Passing a parameter file name to load
          Gordon Savage

          If you reload using a batch file or the command line, you could always pass the name of the file using the /V switch to set the value of a named variable previously declared in the document, for example:

          CALL "C:\PROGRAM FILES\QLIKVIEW\QV.EXE" /R /VVRELOAD=1 "E:\yourpath\yourdocument.QVW"

           

          You could also use 'input' in the script to prompt for a value during script execution - take a look at the help for details.

           

          Regards,

           

          Gordon

            • Passing a parameter file name to load

              Thank you

               

              I did not understand the first approach

              I will summarize very simply.
              The name of my source data changes daily and I wish I could pass as a parameter the name of my data source.

              Initially I a: Load * from [Sales 20120123]

              and the day after the file name is [Sales 20120124]

              can I do a Load * From [Variables]

                • Passing a parameter file name to load
                  Gordon Savage

                  I take it you mean you need to automate the process completely in that you dont want to supply the name of the file every day?

                   

                  If its simply a date then can you just calculate the value from the script execution date (using today(0))?

                   

                  Is the current file the only one in a folder (gets removed after processing?) in which case you could use the wild card '*' to load the folder contents (the only file)

                   

                  Regards,

                   

                  Gordon

                  • Re: Passing a parameter file name to load
                    Martin Goebbels

                    BertyBert,

                     

                    there are really many ways to do it, and you must be aware of exception cases to control, for example if you have to reload data or skip a weekend.

                     

                    The way i suggest is:

                     

                    1 - Create the QVW document and define a variable to hold the reference date (Document Properties - Variables - New button) and define the start date. It's very important not to define the variable directly in script.

                    In my case i had created a variable called vLastLoad with the date of the last successful load (or the day before the first load)

                     

                    2 - create the load script like this:

                     

                    //vNextLoad stores the next date to load

                    LET vNextLoad = DayName(vLastLoad,1) ;

                     

                    //vDateLoad is used to format the date according the date mask used in the input filename

                    LET vDateLoad = date(vNextLoad,'YYYYMMDD');

                     

                    LOAD * FROM

                    Data_$(vDateLoad).dat

                    (txt, codepage is 1252, embedded labels, delimiter is '\t', no quotes);

                     

                    //here I input an IF to save a new reference day only if the load succeeded

                    IF ScriptErrorCount = 0 THEN

                        LET vLastLoad = vDateLoad ;

                    ENDIF

                     

                     

                    You can use the different date functions QlikView offers to set the periodicity of the datafile.

                    If must reset the reference date, you only have to do it in the Document Preferences Window and save the QVW before you restart loading.

                • Re: Passing a parameter file name to load
                  Bryce St Clair

                  I would suggest that you actually have the batch file be written to a specific directory,   say Input.  The script can then read all the files in the directory and then archive the file in to an archive directory.  This all can be done via the executing a command line in the script.  ie

                   

                   

                  let vArchiveCmd = 'cmd.exe /C copy HistData.qvd HistData_bkup.qvd';

                  // &  date(today(),'YYYYMMDD') & timestamp(now(), 'hhmmss') & '.xls';
                  EXECUTE $(vArchiveCmd);

                   

                   

                  let vArchiveCmd = 'cmd.exe /C move HistData.qvd .\Archive';

                  // &  date(today(),'YYYYMMDD') & timestamp(now(), 'hhmmss') & '.xls';
                  EXECUTE $(vArchiveCmd);

                   

                  You can use wild characters and or get the actual file namess.