Qvds Cleanup Script is now working as expected

[comeonqlikview]

Hi Experts,

I have a requirement like need to clean cleanup more than 10 days qvds data with loading qvds data(records) into Qlik only using filename basis.

Below is the script to generate Daily qvds.

T1:
LOAD Date,

Date(Date,'YYYYMMM') as YearMonth,
Date(Date,'YYYY-MM-DD') as Day,
Product,
Sales
FROM
[D:\My Daily Prac\DAILY QVDS\01-01-2025.xlsx]
(ooxml, embedded labels, table is Sheet1);

let vm=NoOfRows('T1');

for i=0 to $(vm)-1;
let vFileName=peek('Day',$(i),'T2');

NoConcatenate
T2:
load *
Resident T1 where Day='$(vFileName)';
STORE T2 into D:\My Daily Prac\DAILY QVDS\$(vFileName).qvd (qvd);
DROP table T2;
next i;

Below Script help us to cleanup the qvds (loading qvd records data )

Set vQvdpath='D:\My Daily Prac\DAILY QVDS\';
Let vCurrentDate= DATE(TODAY(),'YYYY-MM-DD');
Let vDays90DaysAgo= DATE((TODAY()-10),'YYYY-MM-DD');

OldQvdFiles:
Load *,
FileName() AS QvdFileName,
FileTime() AS QvdFileTime,
'Daily' AS TYPE
FROM
[$(vQvdpath)*.qvd](qvd)

WHERE DATE(DATE#(MID(FileName(),Index(FileName(),'DAILY QVDS')+1,10),'YYYY-MM-DD'),'YYYY-MM-DD')<= DATE((TODAY()-10),'YYYY-MM-DD');

FOR i=0 to NoofRows('OldQvdFiles')-1
LET vCurrentQvdFile= Peek('QvdFileName',$(i),'OldQvdfiles');
LET vQvdFilePath='$(vQvdpath)' & vCurrentQvdFile;

TempTable:
Load *
FROM [$(vQvdFilePath)](qvd)
WHERE 0;
STORE TempTable INTO [$(vQvdFilePath)] (qvd);
DROP TABLE TempTable;
TRACE Cleared QVD File: $(vQvdFilePath);
NEXT i;

exit Script;

My Qlik secript is working like While loading data it is fecting records from each qvds and later is clean up but i want to cleanup the qvds withloading data from qvds and using only filename of the qvds.

It should not fetch the records like these when i load it.

It has to work directly like how temp table is loading when we are running the app

Note This can be fine in qliksense and and code has to build without OS commands and Any external scripts.

@sunny_talwar 

[marcus_sommer]

It could be done more simple by using filelist() and within such loop implementing further if-loops which checking the extracted period-information from the file-name.

Take a look on the example-code here: For each..next | Qlik Cloud Help

[comeonqlikview]

Hi @marcus_sommer ,

I am using smae but my incremental script is not maintaining the historical data and giving duplicated records? Can you please correct below script using Qlikview/QlikSense?

SET vQvdpath='D:\Daily Requirements\My Daily Prac\DAILY QVDS\';

Let expecteddate = NUM((Today()-20), 'YYYY-MM-DD');
Let lastDeleteddate = NUM((Today()-21), 'YYYY-MM-DD');

Trace expecteddate;
Trace lastDeleteddate;

MAXDATE:
LOAD
NUM(MAX(DeletedDate)) AS DeletedDate
FROM
[D:\Daily Requirements\My Daily Prac\DAILY QVDS\Cleanupdaily_Data.qvd]
(qvd);

Let vMAXDATE=peek('DeletedDate',0,'MAXDATE');
DROP TABLE MAXDATE;

For Each vfile in FileList('$(vQvdpath)202*.qvd')

TempTable:
LOAD * INLINE [
Dummy

];
Trace filename=$(vfile);
Let date = RIGHT(KeepChar('$(vfile)', '0123456789-'),10);
Trace date=$(date);

//Trace ('$(vDate)' <= '$(expecteddate)' and '$(vDate)' > '$(lastDeleteddate)');
If ('$(date)' <= '$(expecteddate)' and '$(date)' > '$(lastDeleteddate)') Then
Store TempTable into [$(vfile)];
Trace Cleared QVD file:$(date);
// Load new data from the QVD file
FileNameDetails:
LOAD

SubField('$(vfile)', '/', -1) as Filename, // Extract the file name from the full path
KeepChar('$(vfile)', '0123456789-') as ReportingDate, // Extract date from the file name
Num#(Num(FileSize('$(vfile)') / 1024 / 1000, '##.###') & ' MB') as FileSize, // File size in MB
Date((Today()), 'YYYY-MM-DD') as DeletedDate // Current date as Deleteddate
AutoGenerate (1) where NUM(Today())>'$(vMAXDATE)';


End If;

Next vfile

Concatenate(FileNameDetails)
LOAD Filename,
ReportingDate,
FileSize,
DeletedDate
FROM
[D:\MAHESH\Daily Requirements\Mahesh\My Daily Prac\DAILY QVDS\Cleanupdaily_Data.qvd]
(qvd);

Store FileNameDetails into [$(vQvdpath)Cleanupdaily_Data.qvd];

[comeonqlikview]

I am using same source file 

[marcus_sommer]

Finding logically/syntactically issues within a script without running them is quite difficult to impossible because no variable/expression-evaluation is available. But the general approach with tracing the most steps is very helpful to detect the causes of the unexpected behaviour. Within the progress-window it's only for quick glance but in the document-log the steps could be closely analyzed. Helpful may also to comment at first various parts - especially the loads within the loops - to see if the loop-iterations are working and all variables (dates, file-names, ...) have in each the expected values.

Beside this there are various things which could be improved/simplified:

• num() with a date-pattern isn't correct and not necessary by today() - n because it will return a pure number (in regard to trace the date an extra formatting with date() may useful within the development
• the deleted-date mustn't be fetched before the loop-logic else afterwards and stored as variable or table - and the value might be derived and not queried and if not against the data-table else against the system-table
• the dummy-table could be created once before the loop

Further I suggest to re-think the entire task. Is it really useful to overwrite the data with an empty value? I doubt that I would go such a way else just keeping them or removing them completely with triggering batch-statements or more pragmatically doing it once or twice per quarter/year manually (are just seconds).