Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements
The #1 reason QlikView customers adopt Qlik Sense is a desire for a modern BI experience. Read More
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Contributor III
Contributor III

Execute command with Variable and wildcard

Hi,

My script stores qvd files for each day in the format - data_MMDD.qvd.

I have a scenario where at the end of a month, i must delete all the qvd files except the one generated on last day of a month.

example, i must delete all the files from data_0401.qvd to data_0429.qvd

In the script i wrote a variable to find out the last month number which I can pass in the execute statement:

Set vLastMonth=AddMonths(Today(),-1);

Let vLastMonthNum = date(date#($(vLastMonth),'DD-MM-YYYY'),'MM');

I am trying to write an execute statement, something like below, to delete the files using a variable and wildcard but the files are not getting deleted, I do not get any errors. Can somebody please help:

execute cmd.exe /c del data_$(vLastMonthNum)*.txt;

Thanks

1 Solution

Accepted Solutions
Highlighted
Partner
Partner

Re: Execute command with Variable and wildcard

Hi Pooja,

You're right. I got it working using:

Let vLastMonthNum = date(date#(AddMonths(Today(),-1),'DD-MM-YYYY'),'MM');

FOR Each File in filelist ('folder\test_$(vLastMonthNum)*.txt')

     Trace Removing file $(File);

     execute cmd.exe /c del "$(File)";

NEXT File

Let File = Null();

Let vLastMonthNum = Null();

Please find the attached example. Make sure to edit the folder structure from my relative "folder\" to whatever you're using.

Also, for production purposes you might want to remove/comment out the trace command to prevent your log from cluttering.

Hope this helps you.

With kind regards,

Ronald

View solution in original post

9 Replies
Highlighted
Specialist III
Specialist III

Re: Execute command with Variable and wildcard

hello

1 - are you sure of the value of your variable ?

2 - try to enclose the name in quotes

Highlighted
Partner
Partner

Re: Execute command with Variable and wildcard

try

trace execute cmd.exe /c del data_$(vLastMonthNum)*.txt;

to see in your log whether the command gets parsed correctly

Highlighted
Contributor III
Contributor III

Re: Execute command with Variable and wildcard

Hi Ronald,

i checked the log file and see that the wildcard is not parsed, it remains as *. The variable value is getting replaced.

I tried multiple of option, none of these worked.

1) execute cmd.exe /c del "test_$(vLastMonthNum)_*.txt;"

2) execute cmd.exe /c del test_$(vLastMonthNum)_*.txt;

3) execute cmd.exe /c del "test_$(vLastMonthNum)_*".txt;

When i replace the * with a date, its working, but in my case i want it to delete multiple files.

Logfile:

29-05-2018 11:02:44: 0011  SET DayNames='Mon;Tue;Wed;Thu;Fri;Sat;Sun'

29-05-2018 11:02:44: 0013  set vLastMonth=AddMonths(Today(),-1)

29-05-2018 11:02:44: 0014  Let vLastMonthNum = date(date#(AddMonths(Today(),-1),'DD-MM-YYYY'),'MM')

29-05-2018 11:02:44: 0018  execute cmd.exe /c del test_04_*.txt

29-05-2018 11:02:44: 0021  trace this is value 04

29-05-2018 11:02:44: 0021  this is value 04

29-05-2018 11:02:44:      Execution finished.

Thanks

Highlighted
Contributor III
Contributor III

Re: Execute command with Variable and wildcard

Hi Olivier,

my variable gives me the value i am expecting. Tried to enclose the name in quotes, but din work.

Thanks

Highlighted
Partner
Partner

Re: Execute command with Variable and wildcard

Hi Pooja,

You're right. I got it working using:

Let vLastMonthNum = date(date#(AddMonths(Today(),-1),'DD-MM-YYYY'),'MM');

FOR Each File in filelist ('folder\test_$(vLastMonthNum)*.txt')

     Trace Removing file $(File);

     execute cmd.exe /c del "$(File)";

NEXT File

Let File = Null();

Let vLastMonthNum = Null();

Please find the attached example. Make sure to edit the folder structure from my relative "folder\" to whatever you're using.

Also, for production purposes you might want to remove/comment out the trace command to prevent your log from cluttering.

Hope this helps you.

With kind regards,

Ronald

View solution in original post

Highlighted
Specialist III
Specialist III

Re: Execute command with Variable and wildcard

as you try to delete multiple files, a confirmation is asked

try to use del /q

Highlighted
Contributor III
Contributor III

Re: Execute command with Variable and wildcard

Thank you Ronald. That worked !!

Highlighted
Contributor III
Contributor III

Re: Execute command with Variable and wildcard

I also tried the below code, and that worked too.

set vLastMonth=AddMonths(Today(),-1);

set myvar = 'test_$(vLastMonthNum)*.txt';

     execute cmd.exe /c del "$(myvar)";

Highlighted
Partner
Partner

Re: Execute command with Variable and wildcard

I reckon that would be preferable, since you're executing just one command instead of a seperate command for each file.

Upside to the first solution is the added insight into the deleted files since they are all logged by filename.