topic Re: Dynamic execute command with date variable in QlikView

Dynamic execute command with date variable

didierodayo — Tue, 17 Oct 2017 02:58:19 GMT

Hello,

I have a year worth of of qvd and would like to start deleting any qvd older than 6 months. The qvds are saved with the date in the name.

QVDs:

employ_20170131

employ_20170122

employ_20170210

employ_20170930

employ_20171016

Execute command :

EXECUTE CMD.EXE /C del "employ_$vVariable.qvd";

How could I write a dynamic execute query that will go through the qvds and only delete any qvd older than 6 month ago like the ones highlighted in red?

Thanks

Re: Dynamic execute command with date variable

qliksus — Tue, 17 Oct 2017 09:51:03 GMT

Create a filename with the list to be deleted

FOR Each File in filelist (C:\Qlikview\*.qvd)

Filelist:

LOAD

if( date(right($(File)',8),'YYYYMMDD') < date(addmonths(Today() ,-6),'YYYYMM') ,$(File)') as TobeDeleted,

autogenerate 1;

NEXT File

Store that list into a variable

FileDelete:

load concat(distinct chr(39)&TobeDeleted&chr(39),',') as FinalList

Resident Filelist ;

Let Vlist = peek('FileDelete',0,'FinalList') ;

Proceed with the deletion

For each a in $(Vlist)

EXECUTE CMD.EXE /C del $(a) ;

Re: Dynamic execute command with date variable

didierodayo — Tue, 17 Oct 2017 10:59:49 GMT

Hi Susant,

when I debug I can see the script runs up to the FinalList point. then the code then it stops the execute doens't run.

I have allow execute enabled and I have been able to execute a deletion of other files.

my Execute line is

EXECUTE CMD.EXE /C del "C:\Users\.QVD\$(a).qvd";

I have tried without the quote and without the .qvd but nothing.

Thanks

Re: Dynamic execute command with date variable

didierodayo — Tue, 17 Oct 2017 11:29:29 GMT

I think whatever it is it is between the let and the For Each.

Let Vlist = peek('FileToBeDeleted',0,'FinalList') ;

For each a in $(Vlist)

I have tried the following separately and it worked as well.

Let a = 'mytest.qvd'

EXECUTE CMD.EXE /C del "C:\Users\.QVD\$(a)";

Not sure what it going on.

Re: Dynamic execute command with date variable

qliksus — Tue, 17 Oct 2017 11:40:22 GMT

What's the value getting stored into the variable Vlist also just type exit script before the let statement and see whether you can see values in the File list field

Re: Dynamic execute command with date variable

didierodayo — Tue, 17 Oct 2017 11:48:59 GMT

Yes I can see all the values in the Finallist field. So the concat worked. But the variable is not loading. I can't see it after the load.

Re: Dynamic execute command with date variable

didierodayo — Mon, 23 Oct 2017 03:34:49 GMT

Here is the script that worked for anyone with the same issue.

Filelist:

lOAD

FileBaseName() as TobeDeleted

From C:\QVD\*.qvd (qvd)

where Len(Purgechar(Right(FileBaseName(),8), '0123456789.'))=0 and Date#(right(FileBaseName(),8),'YYYYMMDD') < date(addmonths(Today() ,-6),'YYYYMM');

FileToBeDeleted:

load concat(distinct chr(39)&TobeDeleted&chr(39),',') as FinalList

Resident Filelist ;

DROP Table Filelist;

Let Vlist = peek('FinalList',0,'FileToBeDeleted') ;

For each name in $(Vlist)

Let a = 'C:\QVD\$(name).qvd';

EXECUTE CMD.EXE /C del "$(a)";