Qlik Community

QlikView Creating Analytics

Discussion Board for collaboration related to Creating Analytics for QlikView.

Not applicable

Delete from QVD before Load?

Hi,

I have a QVD with a column "DateLoad".

Before saving datas in my QVD, i want to delete all records where Year(DateLoad) = Current Year.

Is it possible?

Thanx for your help.

8 Replies
luciancotea
Valued Contributor

Re: Delete from QVD before Load?

TempTable:

NOCONCATENATE LOAD * RESIDENT Table1

WHERE Year(DateLoad) <> Year(Today());

DROP TABLE Table1;

RENAME TABLE TempTable to Table1;

datanibbler
Esteemed Contributor

Re: Delete from QVD before Load?

Hi,

for processing, you load your qvd into memory anyway, right? And after the processing is all done, you store it as a qvd again.

So just squeeze in a RESIDENT LOAD to exclude those records (using a WHERE clause) and then proceed.

HTH

Best regards,

DataNibbler

Not applicable

Re: Delete from QVD before Load?

This code don't delete datas ?

Could you explain me more? I don't know where i must put the Load * from QVD\file.qvd.

luciancotea
Valued Contributor

Re: Delete from QVD before Load?

There is no "DELETE" command in QlikView. If you load directly from QVD, why don't you:

MyTable:

LOAD *

FROM file.qvd (qvd)

WHERE Year(DateLoad) <> Year(Today());

Not applicable

Re: Delete from QVD before Load?

Ok,

I think that i found the solution.

First table, i load datas from xls files.

Second table, i load datas from QVD

Third table (temp table), noconcatenate resident first table

Fourth table, i store the temp table in my QVD this delete records

Fifth, i store the First table in the QVD file.

Is it true?

luciancotea
Valued Contributor

Re: Delete from QVD before Load?

The logic of your script is a different problem. I just told you how to "delete" rows from a table.

Not applicable

Re: Delete from QVD before Load?

I don't understand the solution...

I explain again.

I load datas from 2 XLS files. I add a column named LoadDate with value TODAY().

Before saving this datas into the QVD files, i want to delete all records from QVD where Year(LoadDateQVD) = Year(Today()).

Exemple :

Xls1:

Load FieldTest,

Today() as LoadDate

From Path\ExcelFile1.xls

Where FieldTest<>'002';

Xls2:

Load FieldTest2,

Today() as LoadDate

From Path\ExcelFile2.xls;

Outer Join

Load *

Resident Xls1;

Drop Table Xls1;

//DELETE FROM QVD Where Year(LoadDate) into QVD = Year(Today())

//Store Xls2 into QVD.

Could you help?

Not applicable

Re: Delete from QVD before Load?

I think that i found the solution...

I've added this script at the top of my script :

DeleteRecords:

Load *

From QVD\File.qvd (qvd)

Where Year(LoadDate)<>Year(Today());


Store DeleteRecords into QVD\File.QVD (qvd);

Drop table DeleteRecords;

Then i load from XLS files and i store result into File.QVD...

Are you OK with this script?

Community Browser