How to delete records from QVD where FLAG=1

[vikasmahajan]

Dear All,

I have ALLDATA.QVD  with  FileName as  AssetTrackingAUG1,SEP1 etc   also  I have another table

Temp_FileNames with Flag .  Now I want to delete records from QVD where Flag = 1 .

Help appreciated.

Thanks in adv

Vikas

[MK_QSL]

Not understood..

You have two fields... one QVD and one Excel.

You want to delete records from QVD where Flag = 1... is this Flag in both files or not?

[vikasmahajan]

Thanks for reply

I have 2 tables ALLDATA.QVD  & Temp_FileNames with Flag (0 or 1)   from ALLDATA.QVD  i want to delete records

who's flag is 1

Vikas

[MK_QSL]

What is the common field between them?

[vikasmahajan]

FileName

[MK_QSL]

FileName is the QVD name or its a field in QVD?

if it is a field in QVD,

T1:

Load * From QVD...
Left Join

Load FileName, Flag From ExcelFile;

NoConcatenate

T2:

Load * Resident T1 Where Flag = 1;

Drop Table T1;

Drop Field Flag;

Strore T2 into T2.qvd(QVD);

[tresesco · Accepted solution]

Try like:

Tab:

Load

          *

From ALLDATA.QVD (qvd);

Inner Join

Load

          *

From Temp_FileNames.qvd (qvd) where Flag =0;

Store Tab into ALLDATA.QVD (qvd);

Drop table Tab;

[vikasmahajan]

Thanks Manish

I use tresesco's approach.

Thanks all

Vikas

[MK_QSL]

Obviously tresesco's approach is better than my approach.

Let me explain you one more scenario..

Consider that in QVD you have below filenames.

FileName

FileName1

FileName2

FileName3

FileName4

and in Excel file you have

FileName          Flag    

FileName1          1    

FileName2          0    

FileName3          1

Now using both of our reply (my one and Tresesco's reply) you will miss FileName4.

You can use below to get FileName4 also in your final QVD.

T1:

Load * From QVD...
Left Join

Load FileName, Flag From ExcelFile;

NoConcatenate

T2:

Load * Resident T1 Where Flag <> 0;

Drop Table T1;

Drop Field Flag;

Strore T2 into T2.qvd(QVD);

If you have exact match of FileName in both tables, you should use tresesco's reply !