How to remove the rows from the QVD Qlik Sense based on the condition

[mvgadagi]

Hello,

Currently, I have the table stored in the QVD and the table contains the column name as Extraction_Date, Before I store the new data to this QVD, I wanted to remove all the rows having the Extraction_Date as Today() ,So even if the script runs multiple times in same day always it removes existing rows and adds the new rows

I have tried as below

1. Check if QVD exists or not before reading from the data
2. If QVD exist then create the table record_daily_count with reading the QVD where Extraction_Date is not today
3. Then create the new temporary table daily_report which contains information of today only
4. Then Concatenate the record_daily_count with daily_report
5. Then drop the table daily_report

After doing this and when I run the scripts multiple times in a day, the rows are appending again and again for the same day, Can you please help me what is wrong here

Script

record_master_list:
	load * from  'lib://Record_Master_Data\record_master_list.qvd'(qvd);

LET vListqvdexists = isnull(QvdCreateTime('lib://Record_Master_Data\record_daily_count.qvd'));

if vListqvdexists = 0 then
  record_daily_count:
  load * from  'lib://Record_Master_Data\record_daily_count.qvd'(qvd) where not [Extraction_Date] <> Today();
  set division = 'A';
  daily_report:
  NoConcatenate
  Load '$(division)' as 'Division', Today() as 'Extraction_Date' Resident record_master_list;
end if


if vListqvdexists <> 0 then
  set division = 'A';
  daily_report:
  NoConcatenate
  Load '$(division)' as 'Division', Today() as 'Extraction_Date' Resident record_master_list;
end if

set division = 'B';

daily_report:
Concatenate(daily_report)
Load '$(division)' as 'Division', Today() as 'Extraction_Date' Resident record_master_list;


if vListqvdexists = 0 then
  record_daily_count:
  Concatenate(record_daily_count)
  load * Resident daily_report;
  store daily_report into 'lib://Record_Master_Data\record_daily_count.qvd'(qvd);
  drop table daily_report;
end If

if vListqvdexists <> 0 then
	store daily_report into 'lib://Record_Master_Data\record_daily_count.qvd'(qvd);
end if

[JordyWegman]

Hi,

Try this. With the where statement you can filter out the rows.

record_master_list:
Load
  *
From  'lib://Record_Master_Data\record_master_list.qvd'(qvd);

// Do your transformations

Final_record_master_list:
Load
  *
Resident [record_master_listX]
Where Floor(Extraction_Date) <> Floor(Today())
;

Store Final_record_master_list INTO  'lib://Record_Master_Data\record_master_list.qvd'(qvd);

Jordy

Climber

[mvgadagi]

No JordyWegman, I wanted to read always record_master_list and compute the daily count reference in record_daily_count , But I am having the problem that record_daily_count should have only 1 record for today's count irrespective of the number of times I run the script on the same day

[Saryk · Accepted solution]

where not [Extraction_Date] <> Today();

This is a double negative, could be

where [Extraction_Date] = Today();

[mvgadagi]

You saved me and the issue was with this. Thanks for identifying this

[Saryk]

Glad I helped !