Dynamic execute command with date variable

[didierodayo]

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

[qliksus]

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)  ;

Next

[didierodayo]

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

[didierodayo]

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.

[qliksus]

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

[didierodayo]

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.

[didierodayo · Accepted solution]

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)";

Next