topic Re: Passing a parameter file name to load in QlikView

Passing a parameter file name to load

Mon, 23 Jan 2012 08:55:49 GMT

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

Mon, 23 Jan 2012 10:21:18 GMT

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

Mon, 23 Jan 2012 10:51:48 GMT

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

Mon, 23 Jan 2012 11:06:50 GMT

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

bimartingo — Fri, 04 May 2012 14:39:57 GMT

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

Wed, 24 Apr 2013 23:04:38 GMT

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.