Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Announcements
QlikWorld 2020: Join us May 11 - 14, 2020 in Phoenix, AZ. Register early and save $400. Learn More
Partner
Partner

Dynamic execute command with date variable

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

Tags (1)
1 Solution

Accepted Solutions
Partner
Partner

Re: Dynamic execute command with date variable

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

View solution in original post

6 Replies
qliksus
Valued Contributor II

Re: Dynamic execute command with date variable

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

Partner
Partner

Re: Dynamic execute command with date variable

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

Partner
Partner

Re: Dynamic execute command with date variable

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
Valued Contributor II

Re: Dynamic execute command with date variable

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

Partner
Partner

Re: Dynamic execute command with date variable

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.

Partner
Partner

Re: Dynamic execute command with date variable

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

View solution in original post