topic Execute command with Variable and wildcard in QlikView

Execute command with Variable and wildcard

poojask123 — Wed, 25 Nov 2020 16:16:04 GMT

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

Re: Execute command with Variable and wildcard

olivierrobin — Mon, 28 May 2018 11:45:09 GMT

hello

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

2 - try to enclose the name in quotes

Re: Execute command with Variable and wildcard

RonaldDoes — Mon, 28 May 2018 12:03:15 GMT

try

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

to see in your log whether the command gets parsed correctly

Re: Execute command with Variable and wildcard

poojask123 — Tue, 29 May 2018 05:34:23 GMT

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

Re: Execute command with Variable and wildcard

poojask123 — Tue, 29 May 2018 05:35:16 GMT

Hi Olivier,

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

Thanks

Re: Execute command with Variable and wildcard

RonaldDoes — Tue, 29 May 2018 06:45:15 GMT

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

Re: Execute command with Variable and wildcard

olivierrobin — Tue, 29 May 2018 10:06:12 GMT

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

try to use del /q

Re: Execute command with Variable and wildcard

poojask123 — Tue, 29 May 2018 10:13:12 GMT

Thank you Ronald. That worked !!

Re: Execute command with Variable and wildcard

poojask123 — Tue, 29 May 2018 12:27:55 GMT

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

Re: Execute command with Variable and wildcard

RonaldDoes — Tue, 29 May 2018 12:31:58 GMT

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.