Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Announcements
Please visit COVID-19 group to keep up the discussion. GO TO GROUP
Highlighted
New Contributor III

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

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



1 Solution

Accepted Solutions
Highlighted
Partner
Partner

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

where not [Extraction_Date] <> Today();

This is a double negative, could be

where [Extraction_Date] = Today();

View solution in original post

5 Replies
Highlighted
Partner
Partner

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

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

 

Work smarter, not harder
Highlighted
New Contributor III

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

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

Highlighted
Partner
Partner

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

where not [Extraction_Date] <> Today();

This is a double negative, could be

where [Extraction_Date] = Today();

View solution in original post

New Contributor III

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

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

Highlighted
Partner
Partner

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

Glad I helped !