topic Re: Scheduling a QV script in QlikView

Scheduling a QV script

Wed, 08 Apr 2015 09:56:16 GMT

Hey Members,

I have a QV script from which I daily import data. I want to schedule this task Please help me out with these simple things.

1. How to automatically update the file from which QV takes the data.

This is how it shows in script –

FROM

(txt, embedded labels);

I need it to update the date automatically so it can take the latest file.

2. After this need it to reload

3. Export the file into .csv format in some folder.

Do I need to make a BAT file to schedule it?? Or is this possible to schedule it from QV?? I do see an option of schedules in Document Properties.

I am very new in QV with very less background of coding. Your help will be much appreciated.

Re: Scheduling a QV script

pokassov — Wed, 08 Apr 2015 12:58:50 GMT

1. You can use the bat file. And write down the follow script:

"c:\Program Files\QlikView\qv.exe" /r "D:\....\DataExtract.qvw"

2. for generating data you can use this:

$(=date(Today(),'YYYYMMDD'))

3. For export during script you can use command "store tablename into .... (txt) or add trigger after reload with action "Export"

Sergey

Re: Scheduling a QV script

Wed, 08 Apr 2015 13:17:47 GMT

Hi Yahs,

but you have Qlik Server?

Through the server can be scheduled reload a qvw

For load, you can adapt the following script, which I created to load data from QVD files up to seven weeks before:

FOR i = 0 to 7

currWeek = Week(Now())- i
currWeekFile = If (Len($(currWeek))=2, $(currWeek), '0'&$(currWeek))

SET currFileName = WEEKLY\NEW_TPM_PROMO_2015_$(currWeekFile).QVD;

IF not IsNull(QvdTableName('WEEKLY\NEW_TPM_PROMO_2015_' & $(currWeekFile) & '.QVD')) then
Data_table:
LOAD * From $(currFileName) (qvd)
ENDIF

NEXT

Regard

Gennaro

Re: Scheduling a QV script

Wed, 08 Apr 2015 13:44:27 GMT

Hi,

so I need to set this as a variable.

I have tried this --

Let vText = 'Q:\bns\derivProdData\sybasePortfolio\GEF\positions\epsilon\GEF.ep.TRS';
Let vText2 = '.csv';
Let Date = $(date(Today(),'YYYYMMDD')) ;

FROM

$(vText) & $(Date)& $(vText2)
(txt, embedded labels);

Its not working.. Might be I doing some mistake in setting Date Variable. Please help me out here.

Thanks

Re: Scheduling a QV script

Wed, 08 Apr 2015 13:49:54 GMT

Use this:

Let vText = 'Q:\bns\derivProdData\sybasePortfolio\GEF\positions\epsilon\GEF.ep.TRS';
Let vText2 = '.csv';
Let vDate = date(Today(),'YYYYMMDD') ;

LOAD *

FROM

$(vText)$(vDate)$(vText2)
(txt, embedded labels);

Regards

Gennaro

Re: Scheduling a QV script

Wed, 08 Apr 2015 13:52:26 GMT

You have missed Load * before From clausule
and after the FROM, to concatenate the variables, do not have to use &

Re: Scheduling a QV script

pokassov — Wed, 08 Apr 2015 13:57:40 GMT

LET vdt=date(Today(),'YYYYMMDD');

LOAD *

FROM

Q:\bns\derivProdData\sybasePortfolio\GEF\positions\epsilon\GEF.ep.TRS.$(vdt).csv

(txt, embedded labels);

Re: Scheduling a QV script

Thu, 09 Apr 2015 09:41:09 GMT

I have tried setting trigger to export the file. (OnPostReload).

How can I export this in a specific filename format with again date as a varible ?

Thanks

Re: Scheduling a QV script

pokassov — Thu, 09 Apr 2015 09:45:46 GMT

Hi!

Use the same way and create variable

Re: Scheduling a QV script

Tue, 09 Jun 2015 11:06:45 GMT

Hey Gennaro,

I am able to reload and not able to export the file. I am using following script -

SET DateFormat='M/D/YYYY';

SET TimestampFormat='M/D/YYYY h:mm:ss[.fff] TT';

SET MonthNames='Jan;Feb;Mar;Apr;May;Jun;Jul;Aug;Sep;Oct;Nov;Dec';

SET DayNames='Mon;Tue;Wed;Thu;Fri;Sat;Sun';

Let vdt= date(Today()-1,'YYYYMMDD');

Let Export= 'Q:\bns\derivProdData\spreadsheets\GEF\VG\Extract\SBI Positions Extract Test'& $(vdt)&'.xls';

LOAD TransId as [TransID],

ReferenceAsset as [Reference Asset],

DividendPassThrough as [% Dividend],

FROM

Q:\bns\derivProdData\sybasePortfolio\GEF\positions\epsilon\GEF.ep.TRS.$(vdt).csv

(txt, embedded labels);

$(Export)

Please advise

Re: Scheduling a QV script

Anonymous — Tue, 09 Jun 2015 12:13:19 GMT

Let Export= 'Q:\bns\derivProdData\spreadsheets\GEF\VG\Extract\SBI Positions Extract Test'& $(vdt)&'.xls';

// you need to give some table name

Table1:

LOAD TransId as [TransID],

ReferenceAsset as [Reference Asset],

DividendPassThrough as [% Dividend],

FROM

Q:\bns\derivProdData\sybasePortfolio\GEF\positions\epsilon\GEF.ep.TRS.$(vdt).csv

(txt, embedded labels);

//$(Export)

//now store this table to a file

Store Table1 into Path/Filename.csv (txt);

Re: Scheduling a QV script

Tue, 09 Jun 2015 12:42:45 GMT

Thanks Kumar

Just a Quick question - I am getting following Script Security pop up message (attached).

Anyway to give system access permanently ?

Re: Scheduling a QV script

Tue, 09 Jun 2015 12:52:10 GMT

Also, I have used to some filters in the table via Current Selection.

How can I incorporate that in code ?

Re: Scheduling a QV script

Peter_Cammaert — Tue, 09 Jun 2015 13:04:56 GMT

This has nothing to do with the previous discussion (and you should create a new thread about this). The message box tells you that an EXECUTE statement is being used in your load script and that the checkbox Script Editor->Bottom pane:Settings->Open Database in Read-Write mode has been enabled. These are serious extensions of typical security settings.

Do you really need to execute command line programs outside QlikView and to write to your DB? Then select "Give System Access"

Peter

Re: Scheduling a QV script

Tue, 09 Jun 2015 14:51:27 GMT

Thanks Peter

Is there any way to store table only currently selected fields ?

Re: Scheduling a QV script

Peter_Cammaert — Tue, 09 Jun 2015 14:57:42 GMT

What do you mean by "Currently selected fields"?

A simple STORE statement will create a QVD from the internal table with the fields you have loaded.

An extended STORE only dumps the fields you name into the QVD.

Peter

Re: Scheduling a QV script

Tue, 09 Jun 2015 15:09:39 GMT

I mean if I have a data for 4 companies and I want to select only for 1 company.

I was searching few more threads. I saw someone suggesting to use -

use variable like v1 with expression

=(SubField(GetcurrentSelections(),':',-1)) // for particular selection

Re: Scheduling a QV script

Peter_Cammaert — Tue, 09 Jun 2015 15:16:37 GMT

If you don't want the data for the other three companies, then add a WHERE clause to your script and do not load their data.

Peter

Re: Scheduling a QV script

Peter_Cammaert — Tue, 09 Jun 2015 15:20:26 GMT

If you want to store the data for companies you selected in the User Interface, you can't do this with a STORE statement, as a STORE writes an internal table to disk including all selected and unselected companies.

On the other hand, in the UI you can use the rightmousemenu and select export. That option offers you to export the data from the currently selected object/chart/table box/list box in a variety of file formats. Such an export will only write the data that is associated with the currently selected company.

Peter

Re: Scheduling a QV script

Tue, 09 Jun 2015 15:20:59 GMT

Can you provide me an example how to use where clause ?