topic Re: Delete from QVD before Load? in QlikView

Delete from QVD before Load?

Wed, 04 Mar 2015 14:22:25 GMT

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.

Re: Delete from QVD before Load?

luciancotea — Wed, 04 Mar 2015 14:30:16 GMT

TempTable:

NOCONCATENATE LOAD * RESIDENT Table1

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

DROP TABLE Table1;

RENAME TABLE TempTable to Table1;

Re: Delete from QVD before Load?

datanibbler — Wed, 04 Mar 2015 14:32:04 GMT

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

Re: Delete from QVD before Load?

Wed, 04 Mar 2015 14:46:37 GMT

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.

Re: Delete from QVD before Load?

luciancotea — Wed, 04 Mar 2015 14:57:14 GMT

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

Re: Delete from QVD before Load?

Wed, 04 Mar 2015 15:01:46 GMT

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?

Re: Delete from QVD before Load?

luciancotea — Wed, 04 Mar 2015 15:31:27 GMT

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

Re: Delete from QVD before Load?

Thu, 05 Mar 2015 06:53:15 GMT

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?

Re: Delete from QVD before Load?

Thu, 05 Mar 2015 07:22:56 GMT

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?